from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from dateutil.parser import parse
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from dateutil import parser
import os
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()
Two amplifier RIAA phono preamp
Federal Realty Investment Trust (FRT)
Last update: 7 Apr 2023
annual report: 2022
share price data: 4/7/2023 \$97.74, beta 1.22
Abstract
This notebook was developed to use as a template to analyze dividend paying companies as potential investments. This company was selected for analysis because it is on the Dividend Aristocrat list and passes the quick look tests.
Introduction
Federal Realty is a recognized leader in the ownership, operation and redevelopment of high-quality retail-based properties located primarily in major coastal markets from Washington, D.C. to Boston as well as San Francisco and Los Angeles.
The stated primary business objective is to own, manage, acquire and redevelop a portfolio of high quality retail focused properties that will: - provide increasing cash flow for distribution to shareholders - generate higher internal growth than the shopping center industry over the long term - provide potential for capital appreciation - protect investor capital
The company specializes in the ownership, management, and redevelopment of high quality retail and mixed-use properties located primarily in densely populated and affluent communities in strategically selected metropolitan markets in the Northeast and Mid-Atlantic regions of the United States, as well as in California and South Florida. As of December 31, 2020, the company owned or had a majority interest in community and neighborhood shopping centers and mixed-use properties which are operated as 101 predominantly retail real estate projects comprising approximately 23.4 million square feet.
Sector(s): Real Estate
Industry: REIT—Retail
Bottom line up front
Recommendation: buy
Follow the link to the Conclusion.
Company description
Federal Realty Investment Trust is a real estate investment trust that invests in shopping centers in the Northeastern United States, the Mid-Atlantic states, California, and South Florida.
Revision history:
- 1/10/2022: Copied from VZ notebook and reorganized - Feb 2022: updated quick look, reorganized flow of calculations, corrected usage of financial rates, organized end sections - 23 Mar 2022: Cleaning up financial data spreadsheet. Removed NAIC tab. Removed duplicate reveneu data. - 27 Mar 2022: MFG template copied from BMY - 24 Apr 2022: MFG template copied and modified for REIT, FRT is analyized in the REIT-template - 3 May 2022: replaced np.linalg.lstsq with np.polyfit in NAIC forecast, added Future forecast based on historical data notes, Dilution notes, decision model - 4 May 2022: saved as BANK-template analysis
Analysis
The following sections of this notebook contain the financial analysis for the company.
Todo:
- clean up commented code
load other REIT data as check, O and ESSload bank data and check before making BANK templateSee NOTE 9—DIVIDENDS page 86.How can dividends be more than net income?look at other REITs from ETF top holdingsfinish Dilutionadd section on confidence in future forecast based on historical dataclean up code in decision model- copy changes made here back to MFG template
- review Current news
- review most recent quarterly results
- read annual report
- write analysis and commentary
- write conclusions
- spell check
- save as REIT template before final cleanup and upload to github
- upload updated files to github
- write blog report and post
- review blog report to make more interesting
- why should I care about this company
- why the key features are important, what are key features
- add some “because of this…that” flow between and linking the key features
1) Stock screener results
This company was selected to analyze because it is a Dividend Aristocrat.
~~This company was selected from the Fidelity stock screener results. The search results are based on Dividend yield (high and very high, 2.83% and higher), Volume 90 average (high and very high. 535k and higher) and Revenue Growth 5 years (0 or higher). ~~
Current news
A review of the financial news sites from yahoo and google showed the following:
- Federal Realty Investment Trust (NYSE: FRT) is acquiring Kingstowne Towne Center in Kingstowne, Virginia for a total purchase price of \$200 million. The acquisition will close in two phases. Federal Realty has closed on phase one of the acquisition for \$100 million and expects to close on phase two for \$100 million in July, subject to customary closing conditions. Combined, the property comprises 410,000 square feet of retail space on 45 acres of land. Located in Virginia’s Fairfax County near TSA’s new headquarters, Kingstowne Towne Center is surrounded by 5,200 homes, four commercial office buildings, and a planned multifamily development, and is part of a one million-square-foot regional retail node that attracts approximately 8.3 million visits annually—amongst the most visited retail destinations in Virginia. - Through the fiscal year ended December 31, 2021, the business of the registrant was conducted by an entity known as Federal Realty Investment Trust, a Maryland real estate investment trust (the “Predecessor”). On December 2, 2021, the Predecessor’s Board of Trustees approved the reorganization of the Predecessor’s business into an umbrella partnership real estate investment trust, or “UPREIT.”
Review quarterly results
Since this analysis mainly looks at the annual reports, a review of the quarterly reports and the most recent 12 months is needed to see if the recent quarterly trends match the yearly trends. - yahoo finance shows TTM Total Revenue is about equal to the most current 10K revenue. - The Compustat Company Research from Fidelity (from Sep. 29, 2021) shows: not reviewed
Average daily volume
Average daily volume: 499,387
Dividend yield
Forward dividend yield: 3.51%
2) Load financial spreadsheet
Data from consolidated financial statements and annual reports was collected and entered into a spreadsheet. All numerical data is converted from thousands or millions of dollars to dollars. The stock share price history was obtained from yahoo and is included as a tab in the spreadsheet. Other tabs in the spreadsheet are various worksheets.
= 'FRT' # company ticker symbol
ticker '/home/jim/Documents/Dividend Investing/DCF data/')
os.chdir(
= ticker+'_Financials.xlsx'
file_name = pd.read_excel(file_name,sheet_name='DCF data')
df_dcf_sheet #df_NAIC_financials = pd.read_excel(file_name,sheet_name='NAIC data')
= pd.read_excel(file_name,sheet_name='metrics')
df_metrics_sheet = pd.read_excel(file_name,sheet_name='Historical Prices')
df_price_history
# change the working director back to the Jupyter folder
'/home/jim/Documents/JupyterLab/Discount Cash Flow Analysis/') os.chdir(
# convert dates from string to datetime format in stock price history
= []
price_date_list for i in range(len(df_price_history)):
str(df_price_history['Date'][i]), '%Y-%m-%d'))
price_date_list.append(datetime.strptime(
0, 'datetime', price_date_list) # insert a new column with datetime data
df_price_history.insert(=['datetime'], inplace=True) # sort data frame by datetime
df_price_history.sort_values(by
'datetime',inplace=True)
df_price_history.set_index(
#df_price_history.head()
2.1) Format data frame
Generate a new data frame that holds the financial data needed for the DCF model. Data from financial statements is copied into a spreadsheet which contains the data used in the analysis. The data in the DCF_data tab is in a consistent format for ease of use by this notebook. Standard names are used for the rows and columns.
#column names: fiscal years
= df_dcf_sheet.columns[1:].values.astype('datetime64[Y]')-1970
fy_data #line 0: Total revenue
= df_dcf_sheet.iloc[0].to_numpy()[1:].astype('float')
revenue_data #line 1: Cost of goods sold
= df_dcf_sheet.iloc[1].to_numpy()[1:].astype('float')
Cost_of_goods_sold_data #line 2: General and administrative
= df_dcf_sheet.iloc[2].to_numpy()[1:].astype('float')
General_and_administrative_data #line 3: Research and development
= df_dcf_sheet.iloc[3].to_numpy()[1:].astype('float')
Research_and_development_data #line 4: Depreciation and amortization
= df_dcf_sheet.iloc[4].to_numpy()[1:].astype('float')
Depreciation_and_amortization_data #line 5: Investment
= df_dcf_sheet.iloc[5].to_numpy()[1:].astype('float')
Investment_data # line 6: Income before income taxes
= df_dcf_sheet.iloc[6].to_numpy()[1:].astype('float')
Income_before_income_taxes_data # line 7: Income tax
= df_dcf_sheet.iloc[7].to_numpy()[1:].astype('float')
Income_tax_data # line 8: Accounts receivable
= df_dcf_sheet.iloc[8].to_numpy()[1:].astype('float')
Accounts_receivable_data # line 9: Inventories
= df_dcf_sheet.iloc[9].to_numpy()[1:].astype('float')
Inventories_data # line 10: Accounts payable
= df_dcf_sheet.iloc[10].to_numpy()[1:].astype('float')
Accounts_payable_data # line 11: Current assets
= df_dcf_sheet.iloc[11].to_numpy()[1:].astype('float')
Current_assets_data # line 12: Current liabilities
= df_dcf_sheet.iloc[12].to_numpy()[1:].astype('float')
Current_liabilities_data # line 13: Long term debt
= df_dcf_sheet.iloc[13].to_numpy()[1:].astype('float')
Long_term_debt_data # line 14: Shares outstanding
= df_dcf_sheet.iloc[14].to_numpy()[1:].astype('float') Shares_outstanding_data
# make a new data frame to store selected financial data
= pd.DataFrame(data={
df_dcf_data 'FY':fy_data[::-1],
'revenue':revenue_data[::-1],
'cost_of_goods_sold':Cost_of_goods_sold_data[::-1],
'general_and_administrative':General_and_administrative_data[::-1],
'research_and_development':Research_and_development_data[::-1],
'depreciation':Depreciation_and_amortization_data[::-1],
'investment':Investment_data[::-1],
'income_before_income_taxes':Income_before_income_taxes_data[::-1],
'income_tax':Income_tax_data[::-1],
'accounts_receivable':Accounts_receivable_data[::-1],
'inventories':Inventories_data[::-1],
'accounts_payable':Accounts_payable_data[::-1],
'current_assets':Current_assets_data[::-1],
'current_liabilities':Current_liabilities_data[::-1],
'long_term_debt':Long_term_debt_data[::-1],
'shares_outstanding':Shares_outstanding_data[::-1]
})
#df_dcf_data
3) Discounted cash flow analysis, baseline
Discounted cash flow (DCF) is a valuation method used to estimate the value of an investment based on its expected future cash flows. DCF analysis attempts to figure out the value of an investment today, based on projections of how much money it will generate in the future. In finance, discounted cash flow (DCF) analysis is a method of valuing a security, project, company, or asset using the concepts of the time value of money. The DCF method used in this notebook follows [1].
The value of any financial investment equals the present value of the expected future cash flows, discounted for risk and timing of these cash flows. The DCF method to value stocks is a four step process.
1. Develop a set of future free cash flows for the corporation based on revenue growth, net operating profit margin, income tax rate and fix and working capital requirements. 2. Estimate the discount rate for the cash flows based on expected timing and risk. 3. Discount the cash flows and total them to calculate the value for the corporation as a whole. 4. Subtract the debt, preferred stock value and other claims and divide by the number of shares outstanding to get the intrinsic value.
Sections - Revenue growth rate - Net operating profit margin - Tax rate - Depreciation Rate - Investment Rate - Working Capital Rate - Current Assets - Current Liabilities - Value of Debt Outstanding - Current stock price - Shares outstanding - 10 year treasury bond yield - Bond yield spread to treasury - Preferred stock yield - Equity risk premium - Company specific beta - DCF model inputs - Future cash flows
Future forecast based on historical data
The DCF model uses historical financial data to estimate future cash flows. However, future changes are largely unpredictable, so we assume that the past record can be used as a rough guide to the future. The more questionable this assumption is, the less valuable is the analysis. So the DCF model is more useful when applied to stable well established companies, since companies with stable earnings are easier to forecast.
Revenue growth rate
The revenue growth rate (also sometimes called net sales) of the corporation plus any other revenues associated with the main operations of the business. It does not include dividends, interest income or non-operating income. Historic revenue data is obtained from consolidated income statements. The year over year change in revenue is calculated and converted to a percent, then an average revenue growth rate is calculated.
Adjustments for a REIT
The revenue is from Total revenue on the income statement and includes: - Rental income - Mortgage interest income - other - Management and other fees from affiliates
Exclude other income, gains on sale of real estate and interest expenses.
# calculate the percent change in revenue
= np.zeros(len(df_dcf_data['revenue'].to_numpy())) # percent change in revenue
pcr for i in range(len(df_dcf_data['revenue'].to_numpy()[0:-1])):
+1] = ((df_dcf_data['revenue'].to_numpy()[i+1] - df_dcf_data['revenue'].to_numpy()[i])/
pcr[i'revenue'].to_numpy()[i+1])*100
df_dcf_data[
= 100
width
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'Revenue, $B')
ax1.set_ylabel(
# plot revenue as single bar
'FY'],df_dcf_data['revenue']/1e9, width,color='k')
plt.bar(df_dcf_data[
='y')
ax1.tick_params(axis
plt.grid()
# instantiate a second y-axes that shares the same x-axis
= ax1.twinx()
ax2 = 'tab:green'
color
'FY'],pcr,'+-g')
ax2.plot(df_dcf_data[
'% Change in revenue',color=color)
ax2.set_ylabel(='y', labelcolor=color)
ax2.tick_params(axis0,50))
ax2.set_ylim((
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Revenue')
plt.title( plt.show()
Observation:
Except for 2020, revenues have grown every year. If 2020 and 2021 is removed from the revenue data series:
#exclude 2021 and 2020 from average calculation
print('average revenue growth rate 2015 to 2019: {:.2f}%'.format(pcr[-7:-2].mean()))
average revenue growth rate 2015 to 2019: 2.04%
= pcr[-5:].mean()/100 # last five years
rgr_avg print('average revenue growth rate: {:.2f}%'.format(rgr_avg*100))
average revenue growth rate: 4.03%
Net operating profit margin
Net Operating Profit should reflect the future revenue generating ability and expense requirements of the operating business that comprise the ongoing operations of the company.
\(\text{NOPM} = \frac{\text{Revenue} - \text{Expenses}}{\text{Revenue}}\)
\(\text{Expenses} = \text{Cost of Goods Sold (CGS)} + \text{General and Administrative (G&A)} + \text{Research and Development (R&D)}\)
General and Administrative (G&A) is also called Sales, General and Administrative (SG&A)
Adjustments for a REIT
G&A and R&D costs are zero. Total operating expenses include Depreciation and amortization, so this item is removed from CGS.
Total expenses is made up of: - Rental expenses - Real estate taxes - General and administrative - Depreciation and amortization
These are all lumped into CGS and Depreciation and amortization is removed. Depreciation and amortization is a non-cash charge, therefore, we add back the charge to total expenses. The idea is that Depreciation and amortization as an expense unfairly reduces our net income because realestate its value over the period.
# NOP = (Revenue - Expenses)
= df_dcf_data['revenue'].to_numpy() - df_dcf_data['cost_of_goods_sold'].to_numpy()
nop
# net operating profit margin as percent of revenue
= nop/df_dcf_data['revenue'].to_numpy()
nopm
# plot as grouped bar chart with labels on right and working capital rate on left
# calculate position of bars
= []
x1_bar_position = []
x2_bar_position for i in df_dcf_data['FY']:
-relativedelta(months=1))
x1_bar_position.append(i+relativedelta(months=1))
x2_bar_position.append(i
= 40 # the width of the bars
width
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'Net operating profit, \\$B')
ax1.set_ylabel(
'cost_of_goods_sold'].to_numpy()/1e9, width,label='CGS')
ax1.bar(x1_bar_position,df_dcf_data[/1e9, width,label='NOP')
ax1.bar(x2_bar_position,nop
='y')
ax1.tick_params(axis#ax1.set_ylim((0,0.7))
ax1.legend()
plt.grid()
# instantiate a second y-axes that shares the same x-axis
= ax1.twinx()
ax2 = 'tab:cyan'
color
'FY'],nopm*100,'+-c')
ax2.plot(df_dcf_data[
'% NOPM',color=color)
ax2.set_ylabel(='y', labelcolor=color)
ax2.tick_params(axis0,100))
ax2.set_ylim((
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Net Operating Profit')
plt.title( plt.show()
Observation
The NOP has been between 0.4 and 0.5 billion dollars during the past few years. The average net operating profit margin for the last 5 years is calculated below.
#Average net operating profit margin
= nopm[-5:].mean()
nopm_avg print('average net operating profit margin: {:.2f}%'.format(nopm_avg*100))
average net operating profit margin: 62.41%
Tax rate
Tax payments are taken from the consolidated income statement, provision for income taxes. The effect of taxes on profits is accounted for.
\(\text{Tax rate} = \frac{\text{Income taxes}}{\text{Income before income taxes}}\)
Adjustments for a REIT
REITs have very little income tax. Income average tax rate is set to zero.
# Average tax rate
= 0 #tax_rate[-5:].mean()
tax_rate_avg print('average tax rate: {:.2f}%'.format(tax_rate_avg*100))
average tax rate: 0.00%
Depreciation Rate
The depreciation rate is used to project the future net investment cash flows. The effect is to reduce the amount of FCFF. Depreciation amounts are from the Consolidated Statement of Cash Flows, Depreciation and Amortization.
\(\text{Depreciation Rate}=\frac{\text{Depreciation and Amortization}}{\text{Revenues}}\)
Depreciation is the write off or expensing of a percentage of the historical cost of an asset over the asset’s useful life. Property, plant and equipment (PP&E) are long term or non current assets owned or controlled by the company and used to manufacture and or sell the company’s products. The balance sheet typically shows all categories of PP&E grouped together, net of accumulated depreciation. Depreciation represents wear and tear on an asset or the fact that an asset gets used up over time. Companies record depreciation expense in the income statement every year for all depreciable assets in service or used by the company during the year. The difference between GAAP and Tax Accounting methods is handled through deferred taxes.
Amortization is the write off or expensing of the cost of a financial instrument or an intangible asset over the shorter of its useful life or legal life. Amortization is similar to depreciation and reflects the declining useful life and value of the intangible asset over time. Companies in research and development intensive fields typically have many patents. Such industries include high technology, pharmaceuticals and chemicals.
# depreciation rate
= df_dcf_data['depreciation'] / df_dcf_data['revenue'].to_numpy()
depreciation_rate
# plot depreciation on left and rate on right
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'dollars, $B')
ax1.set_ylabel(
'FY'],df_dcf_data['depreciation']/1e9, width=100,color='k')
ax1.bar(df_dcf_data[
='y')
ax1.tick_params(axis
plt.grid()
# instantiate a second y-axes that shares the same x-axis
= ax1.twinx()
ax2 = 'tab:Blue'
color
'FY'],depreciation_rate*100,'+-')
ax2.plot(df_dcf_data[
'% Depreciation rate',color=color)
ax2.set_ylabel(='y', labelcolor=color)
ax2.tick_params(axis0,50))
ax2.set_ylim((
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Depreciation')
plt.title( plt.show()
Observation:
Depreciation has been running at about a consistant 30% of revenues.
# average depreciation rate
= depreciation_rate[-5:].mean()
depreciation_rate_avg print('average depreciation rate: {:.2f}%'.format(depreciation_rate_avg*100))
average depreciation rate: 28.08%
Investment Rate
Taken from Consolidated Statement of Cash Flows, Cash used for investing activities. Net investment in the dollar amount needed to support the growth of the firm. Included investments in properties, plant equipment in excess of the depreciation expenses associated with past investments. Net investment decreases the amount of money available to the stockholders. Investment in property, plant and equipment is necessary to both maintain service and sales and also to grow revenues and profits. Investment amounts should include capital expenditures and research and development.
\(Ir=\frac {\text {Capital Expenditures}}{\text{Revenues}}\)
For this company, the yearly investment amounts are taken from the Consolidated Statements of Cash Flows, Net Cash Used in Investing Activities.
Adjustments for a REIT
The yearly investment amounts are taken from the Consolidated Statements of Cash Flows, Net Cash Used in Investing Activities
# investment rate
= df_dcf_data['investment'] / df_dcf_data['revenue'].to_numpy()
investment_rate
# plot investment on left and rate on right
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'dollars, \\$B')
ax1.set_ylabel(
'FY'],df_dcf_data['investment']/1e9, width=100,color='k')
ax1.bar(df_dcf_data[
='y')
ax1.tick_params(axis
plt.grid()
# instantiate a second y-axes that shares the same x-axis
= ax1.twinx()
ax2 = 'tab:Blue'
color
'FY'],investment_rate*100,'+-')
ax2.plot(df_dcf_data[
'% New Investment Rate',color=color)
ax2.set_ylabel(='y', labelcolor=color)
ax2.tick_params(axis0,100))
ax2.set_ylim((
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'New Investment')
plt.title( plt.show()
Observation:
# average investment rate
= investment_rate[-5:].mean()
investment_rate_avg print('average investment rate: {:.2f}%'.format(investment_rate_avg*100))
average investment rate: 48.29%
Working Capital Rate
Working capital is needed to support the corporate sales effort of any company. Often a company’s incremental change in net working capital either positive or negative is approximately proportional to its change in revenue.
\(\text{Working capital} = \text{Accounts Receivable} + \text{Inventories} - \text{Accounts Payable}\)
Working capital is a company’s net investment in its accounts receivable and its inventories (cash outflows), minus its accounts payable (a cash inflow). Working capital and taxes are cash outflows from the corporation that are not available to pay debts and stockholders.
Adjustments for a REIT
For a REIT, the working capital rate is set to zero. REITs generally have no data for inventories and accounts receivable.
# average working capital rate
= 0 #working_capital_rate[-5:].mean()
working_capital_rate_avg print('average working capital rate: {:.2f}%'.format(working_capital_rate_avg*100))
average working capital rate: 0.00%
Current assets
Total Current Assets from the most recent balance sheet statement of the company. Current assets include inventory, cash and accounts receivables.
Adjustments for a REIT
Current assets are in cluded in total assets for a REIT. Current assets are calculted as follows:
\(\text{Current assets} = \text{Cash and cash equivalents} + \text{Accounts and notes receivable}\)
# plot Short Term Assets
= 100 # the width of the bars
width
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
'FY'],df_dcf_data['current_assets']/1e9, width)
plt.bar(df_dcf_data[
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Current assets')
plt.title('dollars, \\$B')
plt.ylabel(
plt.grid() plt.show()
Observation:
An increase in cash and cash equivalents for 2019 on account of the pandemic.
= df_dcf_data['current_assets'].iloc[-1]
sta print('Current assets: ${:.2f}B'.format(sta/1e9))
Current assets: $0.28B
Current liabilities
Total Current Liabilities from the most recent balance sheet consolidated statement.
Adjustments for a REIT
Current Liabilities are calculted as follows:
\(\text{Current liabilities} = \text{Notes payable, net} + \text{Accounts payable and accrued expenses} + \text{Security deposits payable}\)
# plot Short Term Liabilities
= 100 # the width of the bars
width
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
'FY'],df_dcf_data['current_liabilities']/1e9, width)
plt.bar(df_dcf_data[
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Current liabilities')
plt.title('dollars, \\$B')
plt.ylabel(
plt.grid() plt.show()
print('Average of current liabilities: ${:.2f}B'.format(df_dcf_data['current_liabilities'].mean()/1e9))
Average of current liabilities: $0.48B
Observation:
Other current liabilities increased in 2019 and 2020.
= df_dcf_data['current_liabilities'].iloc[-1]
stl print('Current liabilities: ${:.2f}B'.format(stl/1e9))
Current liabilities: $0.82B
Value of Debt Outstanding
Amount of debt outstanding from the most recent balance sheet of the company.
Adjustments for a REIT
Value of Debt Outstanding (long term debt) is calculted as follows:
\(\text{Value of Debt Outstanding} = \text{Total liabilities} - \text{Current liabilities}\)
# calculate the percent change in debt, pcd
= np.zeros(len(df_dcf_data['long_term_debt'].to_numpy())) # percent change in debt
pcd for i in range(len(df_dcf_data['long_term_debt'].to_numpy()[0:-1])):
+1] = ((df_dcf_data['long_term_debt'].to_numpy()[i+1] - df_dcf_data['long_term_debt'].to_numpy()[i])/
pcd[i'long_term_debt'].to_numpy()[i+1])*100
df_dcf_data[
= 100
width
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'Dollars, $B')
ax1.set_ylabel(
# plot revenue as single bar
'FY'],df_dcf_data['long_term_debt']/1e9, width,color='k')
plt.bar(df_dcf_data[
='y')
ax1.tick_params(axis
plt.grid()
# instantiate a second y-axes that shares the same x-axis
= ax1.twinx()
ax2 = 'tab:green'
color
'FY'],pcd,'+-g')
ax2.plot(df_dcf_data[
'% Change in debt',color=color)
ax2.set_ylabel(='y', labelcolor=color)
ax2.tick_params(axis#ax2.set_ylim((-40,100))
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'debt')
plt.title( plt.show()
= pcd[1:].mean()/100
dgr_avg print('average debt growth rate: {:.2f}%'.format(dgr_avg*100))
average debt growth rate: 6.82%
Observation:
As of December 31, 2021, FRT had approximately \$4.1 billion of debt outstanding. Of that outstanding debt, approximately \$341.6 million was secured by all or a portion of 7 of their real estate projects. As of December 31, 2021, approximately 92.6% of FRT debt was fixed rate or fixed via interest rate swap agreements, which includes all of their property secured debt and our unsecured senior notes. FRT organizational documents do not limit the level or amount of debt that they may incur. The amount of our debt outstanding from time to time could have important consequences to our shareholders.
Rising interest rates could adversely affect our cash flow and the market price of our outstanding debt and preferred shares. Of FRT’s \$4.1 billion of debt outstanding as of December 31, 2021, approximately \$356.5 million bears interest at a variable rate, of which, \$300.0 million is unsecured term loan that bears interest at a variable rate of LIBOR plus 80 basis points and \$56.5 million in mortgages payable that bear interest at a variable rate of LIBOR plus 195 basis points and are effectively fixed through two interest rate swap agreements.
FRT also has a \$1.0 billion revolving credit facility, on which no balance was outstanding at December 31, 2021, that bears interest at LIBOR plus 77.5 basis points.
As of December 31, 2021, there is no balance outstanding on FRT’s \$1.0 billion unsecured revolving credit facility and they had cash and cash equivalents of \$162.1 million. FRT also had outstanding forward sales agreements for net proceeds of \$264.0 million as of December 31, 2021. FRT has no debt maturing until June 2023.
In addition, an increase in market interest rates may lead purchasers of FRT debt securities and preferred shares to demand a higher annual yield, which could adversely affect the market price of FRT’s outstanding debt securities and preferred shares and the cost and/or timing of refinancing or issuing additional debt securities or preferred shares.
For the year ended 2021, the weighted average amount of borrowings outstanding on our revolving credit facility was \$19.6 million, and the weighted average interest rate, before amortization of debt fees, was 0.9%.
The interest rates on these mortgages range from 3.91% to 5.00%.
= df_dcf_data['long_term_debt'].iloc[-1]
vod print('Total long term debt and other: ${:.2f}B'.format(vod/1e9))
Total long term debt and other: $4.20B
Current stock price
Most recent stock price for the company. The current stock price is used to calculate the market value of the firm. Use the market value when looking at market capitalization for common stock.
= 97.74 #95.93 # current stock price
csp print('current stock price: ${:,.2f}'.format(csp))
current stock price: $97.74
Dilution
Dilution occurs when a company issues new shares that result in a decrease in existing stockholders’ ownership percentage of that company. Stock dilution can also occur when holders of stock options, such as company employees, or holders of other optionable securities exercise their options. When the number of shares outstanding increases, each existing stockholder owns a smaller, or diluted, percentage of the company, making each share less valuable.
Investigate why there is a historic growth trend in number of shares outstanding. Search annual report for dilutive actions: - share sales - convertable debt - employee options
Search results:
10 year treasury bond yield
The 10 year treasury yield is used as a measure of the risk free rate.
Yield: 3.45%
iShares 7-10 Year Treasury Bond ETF (IEF)
Average Yield to Maturity: 3.5%
= (3.45+3.5)/2/100 # 10 year treasury bond yield, average of data from sources listed above
tby print('10 year treasury bond yield: {:,.2f}%'.format(tby*100))
10 year treasury bond yield: 3.48%
Bond yield spread to treasury
The spread to treasury implies that all corporate debt will have a higher yield than yields associated with comparable maturity US Treasury Bonds. The best way to determine default risk is to see how a particular company’s debt is trading in the market and compare it on a spread basis with comparable maturity yields.
Look at the following or use a default rating systems that are published by the three major rating agencies, Standards and Poors Corp, Moody’s Investor Services and Fitch & Company.
PIMCO Active Bond Exchange-Traded Fund (BOND)
Yield: 3.44%
iShares 5-10 Year Investment Grade Corporate Bond ETF (IGIB)
Average Yield to Maturity: 5.15%
iShares 10+ Year Investment Grade Corporate Bond ETF (IGLB)
Average Yield to Maturity: 5.35%
Web resources: - http://www.standardpoor.com/
- http://bond.yahoo.com/rates.html
- http://www.moodys.com/cust/default.asp
- http://www.fitchibca.com/corporate/index.cfm
= ((3.44+5.15+5.35)/3-tby)/100 # bond yield spread (average) to treasury spread
bystt print('Bond yield spread to treasury: {:,.2f}%'.format(bystt*100))
Bond yield spread to treasury: 4.61%
Preferred stock yield
Amount of preferred stock outstanding from the most recent balance sheet of the company.
From the balance sheet:
- Preferred shares, authorized 15,000,000 shares, \$.01 par: 5.0% Series C Cumulative Redeemable Preferred Shares, (stated at liquidation preference $25,000 per share), 6,000 shares issued and outstanding - 5.417% Series 1 Cumulative Convertible Preferred Shares, (stated at liquidation preference \$25 per share), 399,896 shares issued and outstanding
See below for inputs to model.
= (5+5.417)/2/100 # preferred stock yield
psy print('preferred stock yield: {:,.2f}%'.format(psy*100))
= 6000*25000 + 399896*25 # value of preferred stock
vps print('value of preferred stock: {:,.2f}'.format(vps))
preferred stock yield: 5.21%
value of preferred stock: 159,997,400.00
Company specific beta
The Beta used is Beta of Equity. Beta is the monthly price change of a particular company relative to the monthly price change of the S&P 500. The time period for Beta is 5 years when available. This value can be obtained at yahoo finance.
A measure of risk of an individual stock. It measures volatility of return - a higher beta means a higher risk. A financial model that uses Beta as its sole measure of risk (signal factor model) is called a Capital Asset Pricing Model (CAPM).
= 1.22 # company specific beta
beta print('Company specific beta: {:,.2f}'.format(beta))
Company specific beta: 1.22
DCF model inputs
Below are the DCF model inputs. These values were calculated above.
# various rates
= rgr_avg # revenue growth rate
rgr print('revenue growth rate: {:,.2f}%'.format(rgr*100))
= nopm_avg # net operating profit margin
nopm print('net operating profit margin: {:,.2f}%'.format(nopm*100))
= tax_rate_avg # tax rate
tr print('tax rate: {:,.2f}%'.format(tr*100))
= depreciation_rate_avg # depreciation rate (% of revenue)
dr print('depreciation rate: {:,.2f}%'.format(dr*100))
= investment_rate_avg # investment rate (% of revenue)
ir print('investment rate: {:,.2f}%'.format(ir*100))
= working_capital_rate_avg # working capital rate (% of revenue)
wcr print('working capital rate: {:,.2f}%'.format(wcr*100))
revenue growth rate: 4.03%
net operating profit margin: 62.41%
tax rate: 0.00%
depreciation rate: 28.08%
investment rate: 48.29%
working capital rate: 0.00%
Excess return period
The excess return period is based on a judgment call. The authors of [1] use the 1-5-7-10 rule. They group companies into one of four general categories and excess return periods. They use a 10 year excess return period to calculate what they would consider the maximum value. They use a more conservative 1 year, 5 year or 7 year return period to calculate a more reasonable or minimum value.
- 1 year: Boring companies that operate in a highly competitive, low margin industry in which they have nothing particular going for them. - 5 year: Decent companies that have a recognizable name and decent reputation and perhaps a regulatory benefit (utility company), but can’t control pricing or growth. - 7 year: Good companies with good brand names, large companies of scale, good marketing channels and consumer identification (e.g. McDonald’s) - 10 year: Great companies with great growth potential, tremendous marketing power, band names and in-place benefits (e.g. Intel, Microsoft, Coca Cola, Disney)
The excess return period used for the base case is ten years, which should lead to a higher calculated intrinsic value.
# General Inputs
= df_dcf_data['FY'].iloc[-1].year # fiscal year to start excess return period
fy_start = 10 # excess return period, years
erp = df_dcf_data['revenue'].to_numpy()[-1] # starting revenues for excess return period
rev_start print('starting year: {:.0f}'.format(fy_start))
print('excess return period: {:.0f} years'.format(erp))
print('starting revenues: ${:,.2f}B'.format(rev_start/1e9))
print('shares outstanding: {:,.0f}'.format(so))
starting year: 2022
excess return period: 10 years
starting revenues: $1.07B
shares outstanding: 81,353,180
= vps # preferred stock, market value
ps_mv print('preferred stock, market value : ${:,.2f}B'.format(ps_mv/1e9))
= csp*so # common stock, market value
cs_mv print('common stock, market value: ${:,.2f}B'.format(cs_mv/1e9))
preferred stock, market value : $0.16B
common stock, market value: $7.95B
Long Term Debt, Market Value, ltd_mv
Use the book value for long term debt. Various online resources can be used to research this item. These include, Bondsonline and Bloomberg. The book value of debt and preferred stock is an accounting measure that relates to how much money was raised by the company when each security was issued. The market value of debt and the preferred and common stock is the price that specific obligations would trade at in today’s market.
Long term debt for firms can take one of two forms. It can be a long-term loan from a bank or other financial institution or it can be a long-term bond issued to financial markets, in which case the creditors are the investors in the bond. Firms often have long term obligations that are not captured in the long term debt item. These include obligations to lessors on assets that firms have leased, to employees in the form of pension fund and health care benefits yet to be paid, and to the government in the form of taxes deferred. In the last two decades, accountants have increasingly moved towards quantifying these liabilities and showing them as long term liabilities.
= vod # market value of long term debt
ltd_mv = ltd_mv+ps_mv+cs_mv # total market value
tmv print('total market value: ${:,.2f}B'.format(tmv/1e9))
total market value: $12.31B
Cost of Common Equity, cce
The expected excess return a hypothetical average investor would require of a diversified portfolio of stock (assumed beta = 1.0) over the yield on the 10-year Treasury Bond. The annual rate of return that an investor expects to earn when investing in shares of a company is known as the cost of common equity. It includes dividends and increases in the market value.
= tby+beta*eq_rp # cost of common equity or the expected return for the stock
cce print('cost of common equity: {:,.2f}%'.format(cce*100))
cost of common equity: 7.13%
Long Term Debt, Average Yield, ltd_ay
The total cost of long term debt.
= tby+bystt # long term debt average yield
ltd_ay print('long term debt average yield: {:,.2f}%'.format(ltd_ay*100))
long term debt average yield: 8.09%
Long Term Debt, After Tax Yield, ltd_aty
The tax benefits of long term debt. Interest payments are tax deductible for the company.
= ltd_ay*(1-tr) # long term debt after tax yield
ltd_aty print('long term debt after tax yield: {:,.2f}%'.format(ltd_aty*100))
= vod/tmv # weight for long term debt
ltd_pc = ltd_aty*ltd_pc # after tax effect of long term debt
ltd_ate = psy # preferred stock, average yield
ps_ay = ps_ay # preferred stock, average yield
ps_aty print('preferred stock, average yield: {:,.2f}%'.format(ps_aty*100))
= ps_mv/tmv # preferred stock, % capital
ps_pc = ps_aty*ps_pc # preferred stock, after tax effect
ps_ate = cce # common stock, average yield
cs_ay = cce # common stock, after tax yield
cs_aty print('common stock, after tax yield: {:,.2f}%'.format(cs_aty*100))
= cs_mv/tmv # common stock, % capital
cs_pc = cs_aty*cs_pc # common stock, after tax effect
cs_ate print('common stock, after tax effet: {:,.2f}%'.format(cs_ate*100))
= ltd_ate+ps_ate+cs_ate # total after tax effect
tate print('total after tax effect: {:,.2f}%'.format(tate*100))
= ltd_pc+ps_pc+cs_pc # total % Capital
tpc print('total % Capital: {:,.2f}%'.format(tpc*100))
long term debt after tax yield: 8.09%
preferred stock, average yield: 5.21%
common stock, after tax yield: 7.13%
common stock, after tax effet: 4.61%
total after tax effect: 7.43%
total % Capital: 100.00%
Weighted average cost of capital
A company’s weighted average cost of capital (WACC) is the weighted average of the company’s current cost of debt and equity calculated by using current debt, preferred stock and common stock market values. The WACC of the company, calculated after tax, is the discount rate used in the DCF valuation procedures. The WACC, which is the cost of the different components of financing used by the firm, weighted by their market value proportions. These include debt, preferred stock, and common stock.
WACC: Weighted Average Cost of Capital, the rate used to discount cash flows, based on the following three factors. 1. Base rate of return. 2. Expected return based on debt and preferred stock. 3. Expected return on common stock and Beta.
All adjusted for the tax advantage of interest payments and the percentage of debt, preferred stock and common stock.
= tate
wacc print('weighted average cost of capital: {:.1f}%'.format(wacc*100))
weighted average cost of capital: 7.4%
Future cash flows
The future cash flows to the firm are projected based on revenue growth. The cash flows are then discounted using the WACC and the ISV is calculated.
# make a list of the fiscal years in excess return period
= np.zeros(erp+1)
fy 0] = fy_start
fy[for i in range(1,erp+1):
=fy_start+i
fy[i]
= np.zeros(len(fy))
rev = np.zeros(len(fy))
ciwc 0] = rev_start #*rgr+rev_start # find the future revenue using constant revenue growth rate
rev[
for i in range(1,len(fy)):
= rev[i-1]*rgr+rev[i-1] # find the future revenue
rev[i] = (rev[i]-rev[i-1])*wcr # find the change in working capital
ciwc[i]
= np.zeros(len(fy)) # net operating profit
net_op = np.zeros(len(fy))
adj_taxes = np.zeros(len(fy))
nopat = np.zeros(len(fy))
invest = np.zeros(len(fy))
depre = np.zeros(len(fy))
net_invest = np.zeros(len(fy))
fcff = np.zeros(len(fy))
disc_fact = np.zeros(len(fy))
disc_fcff
# calculate values in table
for i in range(1,len(fy)):
= rev[i]*nopm # net operating profit margin
net_op[i] = net_op[i]*tr # net operating profit adjusted for taxes
adj_taxes[i] = net_op[i]-adj_taxes[i] # after tax net operating profit
nopat[i] = rev[i]*ir # future investments
invest[i] = rev[i]*dr # future depreciations
depre[i] = invest[i]-depre[i] # net investments
net_invest[i] = nopat[i]-net_invest[i]-ciwc[i] # free cash flow to the firm
fcff[i] = 1/((1+wacc)**i) # discount factor
disc_fact[i] = disc_fact[i]*fcff[i] # discounted free cash flow to the firm
disc_fcff[i]
= nopat[-1]/wacc*disc_fact[-1] # discounted corporate residual value
dcrv = disc_fcff.sum() # discounted excess return period FCFF
derp_fcff
= derp_fcff+dcrv+sta # total corporate value
tcv = tcv-vod-vps-stl # total value of common equity
tvce = tvce/so # intrinsic stock value
isv
# print cash flows in a table
print('{:4s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}'.format('Year','Rev','NOP','AdjTaxes',
'NOPAT','Invest.','Deprec.','dInvest.','dWC','FCFF','DF','DF*FCFF'))
for i in range(len(fy)):
print('{:4.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.4f}{:10,.0f}'.format(fy[i],
/1e6,net_op[i]/1e6,adj_taxes[i]/1e6,nopat[i]/1e6,invest[i]/1e6,depre[i]/1e6,(invest[i]-depre[i])/1e6,ciwc[i]/1e6,
rev[i]/1e6,disc_fact[i],disc_fcff[i]/1e6)) fcff[i]
Year Rev NOP AdjTaxes NOPAT Invest. Deprec. dInvest. dWC FCFF DF DF*FCFF
2022 1,074 0 0 0 0 0 0 0 0 0.0000 0
2023 1,118 698 0 698 540 314 226 0 472 0.9308 439
2024 1,163 726 0 726 562 327 235 0 491 0.8664 425
2025 1,210 755 0 755 584 340 244 0 510 0.8064 412
2026 1,258 785 0 785 608 353 254 0 531 0.7506 399
2027 1,309 817 0 817 632 368 265 0 552 0.6987 386
2028 1,362 850 0 850 658 382 275 0 575 0.6503 374
2029 1,417 884 0 884 684 398 286 0 598 0.6053 362
2030 1,474 920 0 920 712 414 298 0 622 0.5634 350
2031 1,533 957 0 957 740 430 310 0 647 0.5244 339
2032 1,595 995 0 995 770 448 322 0 673 0.4881 328
# Intrinsic Value
print('discounted excess return period FCFF: ${:,.2f}B'.format(derp_fcff/1e9))
print('discounted corporate residual value: ${:,.2f}B'.format(dcrv/1e9))
print('total corporate value: ${:,.2f}B'.format(tcv/1e9))
print('total value of common equity: ${:,.2f}B'.format(tvce/1e9))
= tvce # save value as baseline case
tvce_baseline = isv # save the isv for the baseline case
isv_baseline print('intrinsic stock value, baseline case: ${:,.2f}'.format(isv_baseline))
print('current stock price: ${:,.2f}'.format(csp))
discounted excess return period FCFF: $3.81B
discounted corporate residual value: $6.53B
total corporate value: $10.63B
total value of common equity: $5.45B
intrinsic stock value, baseline case: $66.99
current stock price: $97.74
Observation:
The base line DCF analysis produces an intrinsic stock value of \$105. Some adjustments will be made in the scenario 1 case.
The calculations used here can be verified by using the Valuepro web site, which calculates ISV based on the same method (not working as of 2/5/2022).
List of all inputs to the DCF model
The following print statements format the inputs to the model similar to how they are presented on the Valuepro page.
print('{:>35s} {:<10.0f} {:>35s} {:,.3f}'.format('Excess return period, years:',erp,'Depreciation rate, %:',dr*100))
print('{:>35s} {:<10,.2f} {:>35s} {:,.3f}'.format('Starting revenues, $B:',
/1e9,'Investment rate, %:',ir*100))
rev_startprint('{:>35s} {:<10,.3f} {:>35s} {:,.3f}'.format('Revenue growth rate, %:',
*100,'Working capital rate, %:',wcr*100))
rgrprint('{:>35s} {:<10,.3f} {:>35s} {:,.3f}'.format('Net operating profit margin, %:',
*100,'Current assets, $B:',sta/1e9))
nopmprint('{:>35s} {:<10,.3f} {:>35s} {:.3f}'.format('Tax rate, %:',
*100,'Current liabilities, $B:',stl/1e9))
trprint('{:>35s} {:<10,.2f} {:>35s} {:,.2f}'.format('Current stock price, $:',
'Equity risk premium, %:',eq_rp*100))
csp,print('{:>35s} {:<10,.0f} {:>35s} {:,.2f}'.format('Shares outstanding, basic, M:',
/1e6,'Company specific beta:',beta))
soprint('{:>35s} {:<10,.2f} {:>35s} {:.3f}'.format('10 year treasury bond yield, %:',
*100,'Total long term debt and other, $B:',vod/1e9))
tbyprint('{:>35s} {:<10,.2f} {:>35s} {:,.3f}'.format('Bond yield spread to treasury, %:',
*100,'Value of preferred stock, $B:',vps/1e9))
bysttprint('{:>35s} {:<10,.2f}'.format('Preferred stock yield, %:',psy*100))
Excess return period, years: 10 Depreciation rate, %: 28.081
Starting revenues, $B: 1.07 Investment rate, %: 48.295
Revenue growth rate, %: 4.029 Working capital rate, %: 0.000
Net operating profit margin, %: 62.407 Current assets, $B: 0.283
Tax rate, %: 0.000 Current liabilities, $B: 0.820
Current stock price, $: 97.74 Equity risk premium, %: 3.00
Shares outstanding, basic, M: 81 Company specific beta: 1.22
10 year treasury bond yield, %: 3.48 Total long term debt and other, $B: 4.202
Bond yield spread to treasury, %: 4.61 Value of preferred stock, $B: 0.160
Preferred stock yield, %: 5.21
# weighted average cost of capital inputs
print('Weighted Average Cost of Capital')
print('Cost of common equity')
print('{:s}'.format('-'*37))
print('{:>32s} {:,.2f}'.format('10 year treasury bond yield, %:',tby*100))
print('{:>32s} {:,.2f}'.format('Company specific beta:',beta))
print('{:>32s} {:,.2f}'.format('Equity risk premium, %:',eq_rp*100))
print('{:s}'.format('-'*37))
print('{:>32s} {:,.2f}'.format('Cost of common equity, %:',cce*100))
print()
print('Market Capitalization and After-Tax Weighted Average Cost of Capital')
print()
print('{:s}{:^10s}{:^10s}{:^10s}{:^15s}{:^15s}'.format(' '*20,'Current','After-Tax','Market','%','Weighted After-'))
print('{:s}{:^10s}{:^10s}{:^10s}{:^15s}{:^15s}'.format(' '*20,'Yield','Yield','Value','Capitalization','Tax Yield'))
print('{:s}'.format('-'*80))
print('{:<15s}{:>12.2f}{:>10.2f}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('Long term debt',
*100,(tby+eq_rp)*(1-tr)*100,vod/1e9,ltd_pc*100,ltd_ate*100))
ltd_ayprint('{:<15s}{:>12.2f}{:>10.2f}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('Preferred stock',
*100,ps_ate*100,vps/1e9,ps_pc*100,ps_ate*100))
psyprint('{:<15s}{:>12.2f}{:>10.2f}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('Common stock',
*100,cs_aty*100,cs_mv/1e9,cs_pc*100,cs_aty*100))
cs_ayprint('{:s}'.format('-'*80))
print('{:<37s}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('',tmv/1e9,tpc*100,wacc*100))
Weighted Average Cost of Capital
Cost of common equity
-------------------------------------
10 year treasury bond yield, %: 3.48
Company specific beta: 1.22
Equity risk premium, %: 3.00
-------------------------------------
Cost of common equity, %: 7.13
Market Capitalization and After-Tax Weighted Average Cost of Capital
Current After-Tax Market % Weighted After-
Yield Yield Value Capitalization Tax Yield
--------------------------------------------------------------------------------
Long term debt 8.09 6.48 4 34.12 2.76
Preferred stock 5.21 0.07 0 1.30 0.07
Common stock 7.13 7.13 8 64.58 7.13
--------------------------------------------------------------------------------
12 100.00 7.43
4) DCF Scenarios
The following adjustments were made to various model parameters. - excess return period was adjusted to a more conservative 5 years - revenue growth rate was adjusted to 5% to reflect pre Covid growth. (base case = 3.037 %) - net operating profit margin was adjusted to 60% (base case = 62.820%) - tax rate was adjusted to 0% (base case = 0%) - depreciation rate was adjusted to 27% (base case = 27.492%) - investment rate was adjust to 53% (base case = 53.184%) - working capital rate was set to an even 0% (base case = 0%) - weighted average cost of capital was adjusted up by 2% to reflect higher interest rates and provide a margin of safety (base case = 3.8%)
print('adjusted DCF input values and rates')
= 5
erp print('excess return period: {:,.0f} years'.format(erp))
= 5/100 # setting growth rate to 5%, since this is more in line with Covid grown
rgr print('revenue growth rate: {:,.1f}%'.format(rgr*100))
= isv_s1_nopm = 60/100
nopm print('net operating profit margin: {:.2f}%'.format(nopm*100))
= isv_s1_tr =0/100
tr print('tax rate: {:.2f}%'.format(tr*100))
= 27/100
dr print('depreciation rate: {:,.2f}%'.format(dr*100))
= 53/100 # investment rate (% of revenue)
ir print('investment rate: {:,.2f}%'.format(ir*100))
= 0/100
wcr print('working capital rate: {:,.1f}%'.format(wcr*100))
= (wacc+0.02) # weighted average cost of capital, increased by 2%
wacc_adj #wacc_adj = 1/100
print('weighted average cost of capital: {:.1f}%'.format(wacc_adj*100))
adjusted DCF input values and rates
excess return period: 5 years
revenue growth rate: 5.0%
net operating profit margin: 60.00%
tax rate: 0.00%
depreciation rate: 27.00%
investment rate: 53.00%
working capital rate: 0.0%
weighted average cost of capital: 9.4%
# make a list of the fiscal years in excess return period
= np.zeros(erp+1)
fy 0] = fy_start
fy[for i in range(1,erp+1):
=fy_start+i
fy[i]
= np.zeros(len(fy))
rev = np.zeros(len(fy))
ciwc 0] = rev_start #*rgr+rev_start # find the future revenue using constant revenue growth rate
rev[
for i in range(1,len(fy)):
= rev[i-1]*rgr+rev[i-1] # find the future revenue
rev[i] = (rev[i]-rev[i-1])*wcr # find the change in working capital
ciwc[i]
= np.zeros(len(fy))
net_op = np.zeros(len(fy))
adj_taxes = np.zeros(len(fy))
nopat = np.zeros(len(fy))
invest = np.zeros(len(fy))
depre = np.zeros(len(fy))
net_invest = np.zeros(len(fy))
fcff = np.zeros(len(fy))
disc_fact = np.zeros(len(fy))
disc_fcff
# calculate values in table
for i in range(1,len(fy)):
= rev[i]*nopm # net operating profit
net_op[i] = net_op[i]*tr # net operating profit adjusted for taxes
adj_taxes[i] = net_op[i]-adj_taxes[i] # after tax net operating profit
nopat[i] = rev[i]*ir # future investments
invest[i] = rev[i]*dr # future depreciations
depre[i] = invest[i]-depre[i] # net investments
net_invest[i] = nopat[i]-net_invest[i]-ciwc[i] # free cash flow to the firm
fcff[i] = 1/((1+wacc_adj)**i) # discount factor
disc_fact[i] = disc_fact[i]*fcff[i] # discounted free cash flow to the firm
disc_fcff[i]
= nopat[-1]/wacc*disc_fact[-1] # discounted corporate residual value
dcrv = disc_fcff.sum() # discounted excess return period FCFF
derp_fcff
= derp_fcff+dcrv+sta # total corporate value
tcv = tcv-vod-vps-stl # total value of common equity
tvce = tvce/so # intrinsic stock value
isv
# print cash flows in a table
print('{:4s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}'.format(
'Year','Rev','NOP','AdjTaxes',
'NOPAT','Invest.','Deprec.','dInvest.','dWC','FCFF','DF','DF*FCFF'))
for i in range(len(fy)):
print('{:4.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.4f}{:10,.0f}'.
format(fy[i],rev[i]/1e6,net_op[i]/1e6,adj_taxes[i]/1e6,nopat[i]/1e6,invest[i]/1e6,depre[i]/1e6,
-depre[i])/1e6,ciwc[i]/1e6,fcff[i]/1e6,disc_fact[i],disc_fcff[i]/1e6)) (invest[i]
Year Rev NOP AdjTaxes NOPAT Invest. Deprec. dInvest. dWC FCFF DF DF*FCFF
2022 1,074 0 0 0 0 0 0 0 0 0.0000 0
2023 1,128 677 0 677 598 305 293 0 384 0.9138 350
2024 1,185 711 0 711 628 320 308 0 403 0.8350 336
2025 1,244 746 0 746 659 336 323 0 423 0.7630 323
2026 1,306 784 0 784 692 353 340 0 444 0.6972 310
2027 1,371 823 0 823 727 370 357 0 466 0.6371 297
# Intrinsic Value
print('discounted excess return period FCFF: ${:,.2f}B'.format(derp_fcff/1e9))
print('discounted corporate residual value: ${:,.2f}B'.format(dcrv/1e9))
print('total corporate value: ${:,.2f}B'.format(tcv/1e9))
print('total value of common equity: ${:,.2f}B'.format(tvce/1e9))
= tvce # save value as scenario 1
tvce_S1 = isv # save the isv for scenario 1 case
isv_S1 print('intrinsic stock value, scenario 1 case: ${:,.2f}'.format(isv_S1))
print('current stock price: ${:,.2f}'.format(csp))
discounted excess return period FCFF: $1.62B
discounted corporate residual value: $7.05B
total corporate value: $8.95B
total value of common equity: $3.77B
intrinsic stock value, scenario 1 case: $46.32
current stock price: $97.74
print('ISV serario 1 to current stock price ratio: {:,.2f}%'.format(isv_S1/csp*100))
ISV serario 1 to current stock price ratio: 47.39%
The DCF model calculates with adjustments an intrinsic stock value of \$90, which is less than the current stock price. Based on ISV serario 1, current price is overvalued by 28%. Adjusting the WACC to 1% would make the ISV about equal to the current stock price.
5) NACI stock selection guide analysis
This analysis follows the NAIC stock selection guide (SSG) [2]. The SSG relates revenue growth, EPS and share price history and makes a prediction about the future share price.
The National Association of Investors Clubs (NAIC) is a nonprofit organization dedicated to educating individual investors and investment clubs to become successful lifelong investors. NAIC’s Stock Selection Guide (SSG) is used in the following cells to analyze the company’s growth and whether the stock is selling at a reasonable price.
The SSG was originally developed in the 1950s as a paper worksheet by the not-for-profit National Association of Investors Corporation (NAIC). The SSG aims to aid individual investors in the fundamental analysis and selection of common stocks by reviewing components of a company’s growth, quality, and value.
Load data from metrics sheet
# column names: fiscal years
= df_metrics_sheet.columns[1:].values.astype('datetime64[Y]')-1970
fy_data # line 0: Net income
= df_metrics_sheet.iloc[0].to_numpy()[1:].astype('float')
net_income_data # line 1: Shareholder equity
= df_metrics_sheet.iloc[1].to_numpy()[1:].astype('float')
shareholder_equity_data # line 2: Total liabilities
= df_metrics_sheet.iloc[2].to_numpy()[1:].astype('float')
total_liabilities_data # line 3: Free cash flow, Net cash provided by operating activities
= df_metrics_sheet.iloc[3].to_numpy()[1:].astype('float')
free_cash_flow_data # line 4: Dividends
= df_metrics_sheet.iloc[4].to_numpy()[1:].astype('float')
dividends_data # line 5: Total assets
= df_metrics_sheet.iloc[5].to_numpy()[1:].astype('float')
total_assets_data # line 6: Earnings per share
= df_metrics_sheet.iloc[6].to_numpy()[1:].astype('float')
eps_data # line 7: Dividends per share
= df_metrics_sheet.iloc[7].to_numpy()[1:].astype('float')
dps_data # line 8: Total tangible assets
= df_metrics_sheet.iloc[8].to_numpy()[1:].astype('float')
total_tangible_assets_data # line 9: Liabilities w/o deposits
= df_metrics_sheet.iloc[9].to_numpy()[1:].astype('float')
liabilities_wo_deposits_data # line 10: Provision for credit losses
= df_metrics_sheet.iloc[10].to_numpy()[1:].astype('float')
provision_for_credit_losses_data # line 11: Short-term borrowings
= df_metrics_sheet.iloc[11].to_numpy()[1:].astype('float')
short_term_borrowings_data # line 12: Preferred stock
= df_metrics_sheet.iloc[12].to_numpy()[1:].astype('float')
preferred_stock_data # line 13: Net cash used in investing activities
= df_metrics_sheet.iloc[13].to_numpy()[1:].astype('float') net_cash_used_in_investing_activities_data
# make a new data frame to store data from metrics sheet
= pd.DataFrame(data={
df_metrics_data 'FY':fy_data[::-1],
'net_income':net_income_data[::-1],
'shareholder_equity':shareholder_equity_data[::-1],
'total_liabilities':total_liabilities_data[::-1],
'free_cash_flow':free_cash_flow_data[::-1],
'dividends':dividends_data[::-1],
'total_assets':total_assets_data[::-1],
'eps':eps_data[::-1],
'dps':dps_data[::-1],
'total_tangible_assets':total_tangible_assets_data[::-1],
'liabilities_wo_deposits':liabilities_wo_deposits_data[::-1],
'provision_for_credit_losses':provision_for_credit_losses_data[::-1],
'short_term_borrowings':short_term_borrowings_data[::-1],
'preferred_stock':preferred_stock_data[::-1],
'net_cash_used_in_investing_activities':net_cash_used_in_investing_activities_data[::-1]
})
#df_metrics_data
check for matching years in both data frames
if all(df_dcf_data['FY'] == df_metrics_data['FY']) != True:
print('error, years in data frame don\'t match')
# this is not python code, so jupyterlab will throw an error
stop else:
print('OK, years in data frame match')
OK, years in data frame match
NAIC section 1: Visual analysis
High and low price history for each year
From the daily price history obtained from yahoo finance, the high and low closing price for each is obtained and the data saved to the financial data frame as new columns.
#column names: fiscal years
= df_metrics_sheet.columns[1:].values.astype('str')[::-1]
years_list
# convert years to datetime format
= []
year_ended_list for i in years_list:
'%Y'))
year_ended_list.append(datetime.strptime(i,
# make emnpy lists to store open, close, high and low price data for each fiscal year
= []
fy_open = []
fy_close = []
fy_high = []
fy_low
for i in year_ended_list:
= i
start = i + relativedelta(years=1)
end = df_price_history.truncate(before=start, after=end)
p1 if len(p1) == 0:
fy_open.append(np.nan)
fy_close.append(np.nan)
fy_high.append(np.nan)
fy_low.append(np.nan)else:
'Open'].iloc[0])
fy_open.append(p1['Close'].iloc[-1])
fy_close.append(p1['Close'].max())
fy_high.append(p1['Close'].min())
fy_low.append(p1[
# convert from list to numpy array
= np.asarray(fy_open)
fy_open = np.asarray(fy_close)
fy_close = np.asarray(fy_high)
fy_high = np.asarray(fy_low) fy_low
Plotting the data
The annual sales, EPS and the high and low share price is plotted on a semilog plot. A consistent percentage change in the data will plot on the semi-log chart as a straight line.
The stock price is plotted separately from the sales and earnings for clarity.
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
# figsize() function to adjust the size
=(15, 5))
plt.subplots(figsize
# using subplot function and creating
# plot one
1, 2, 1)
plt.subplot(= 3 # the width of the bars
width #plt.bar(year_ended_list,fy_high-fy_low, width,bottom=fy_low,label='price')
= 0
j for i in year_ended_list:
= 'green'
color if fy_open[j] > fy_close[j]: color= 'red'
# high/low lines
=color, linewidth=width)
plt.plot([i,i],[fy_low[j],fy_high[j]],color# open marker
-relativedelta(months=1)], [fy_open[j],fy_open[j]], color=color, linewidth=width)
plt.plot([i,i# close marker
+relativedelta(months=1)], [fy_close[j],fy_close[j]], color=color, linewidth=width)
plt.plot([i,i+= 1
j
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
#plt.ylim((20,80))
'Yearly stock high and low price range')
plt.title('stock price, $')
plt.ylabel(#plt.legend()
plt.grid()
# using subplot function and creating plot two
1, 2, 2)
plt.subplot(
'FY'],df_dcf_data['revenue']/1e9,'+-',label='revenue, $B')
plt.plot(df_metrics_data['FY'],df_metrics_data['eps'],'+-',label='EPS, $')
plt.plot(df_metrics_data[
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
#plt.yscale('log')
#plt.yticks([0.1,1,10,100,1000,10000],['0.1','1','10','100','1000','10000'])
#plt.ylim((0.1,1000))
'Revenue and EPS')
plt.title('Revenue and EPS')
plt.ylabel(
plt.legend()
plt.grid()
# space between the plots
4)
plt.tight_layout(
# show plot
plt.show()
Observation:
Share price has been usually trading in the 80 to 140 dollar range the last 5 years, indicating that the market does not see FRT as a growth company. EPS have been eratic over the last 5 years.
NAIC section 3, Price earnings history
Section 3 of the SSG is the Price-Earnings history. The following table is built from the high and low prices each year and the earnings per share. The high and low Price/Earnings ratios are calculated for each year and are listed in the columns labeled h-per and l-per.
print('{:4s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}'.format('year','high','low','eps',
'h-per','l-per'))
for i in range(len(year_ended_list)):
print('{:s}{:10,.2f}{:10,.2f}{:10,.2f}{:10,.2f}{:10,.2f}'.format(year_ended_list[i].strftime("%Y"),
'eps'][i],
fy_high[i], fy_low[i],df_metrics_data[/df_metrics_data['eps'][i],
fy_high[i]/df_metrics_data['eps'][i])) fy_low[i]
year high low eps h-per l-per
2010 84.32 63.07 1.99 42.37 31.69
2011 92.45 75.31 2.29 40.37 32.89
2012 110.03 89.23 2.36 46.62 37.81
2013 117.96 96.21 2.47 47.76 38.95
2014 137.18 100.90 2.42 56.69 41.69
2015 150.27 124.96 3.04 49.43 41.11
2016 170.35 136.98 3.51 48.53 39.03
2017 145.29 120.52 3.97 36.60 30.36
2018 135.55 108.11 3.18 42.63 34.00
2019 141.16 115.81 4.61 30.62 25.12
2020 131.07 65.81 1.62 80.91 40.62
2021 137.12 82.27 3.26 42.06 25.24
2022 139.37 87.91 4.71 29.59 18.66
Average high and P/E for select years
The average price to earning ratio based on high and low stock prices is calculated.
#Average high P/E for years
= (fy_high/df_metrics_data['eps']).mean()
pe_avg_high print('average high P/E {:.2f}'.format(pe_avg_high))
#Average low P/E for years
= (fy_low/df_metrics_data['eps']).mean()
pe_avg_low print('average low P/E {:.2f}'.format(pe_avg_low))
average high P/E 45.71
average low P/E 33.63
Estimate future EPS
Use polyfit to get EPS slope and intercept of a least square fit.
= df_metrics_data['eps']
y = np.arange(len(y))
x = np.polyfit(x, y, 1)
m, c print('EPS slope: {:.2f}'.format(m))
print('EPS intercept: {:.2f}'.format(c))
= m*x + c # data points for each year lstsq_fit
EPS slope: 0.15
EPS intercept: 2.14
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'EPS')
ax1.set_ylabel(
'FY'],df_metrics_data['eps'], 'o',label='EPS')
ax1.plot(df_metrics_data['FY'],lstsq_fit, '-',label='least squares fit')
ax1.plot(df_metrics_data[
='y')
ax1.tick_params(axis#ax1.set_ylim((0,4))
ax1.legend()
plt.grid()
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'EPS and least squares fit')
plt.title( plt.show()
Using the equation for the best fit line, find the y value for the eps point at five years in the future.
# estimated eps in 5 years
= m*(x[-1]+5) + c
eps_5yr_est print('estimated eps in 5 years: {:.1f}'.format(eps_5yr_est))
estimated eps in 5 years: 4.7
Using the high and low price to earning ratio from above and the projected eps, calculate the range of stock price in five years.
= eps_5yr_est*pe_avg_low
naic_price_eps_low = eps_5yr_est*pe_avg_high
naic_price_eps_high print('estimated price range in 5 years: ${:.2f} to ${:.2f}'.format(naic_price_eps_low,naic_price_eps_high))
estimated price range in 5 years: $157.04 to $213.44
This is the estimated price range of the stock based on projected EPS and is a guide for what the stock price might be if conditions remain the same. Since the slope of the EPS history is negative, the projected stock price is negative.
NAIC section 3: 5 year estimated EPS, preferred method
See page 87 and figure 10-1, Need the following data:
- estimate sales in 5 years based on sales growth - NOPM - Tax rate - shares outstanding
Net Operating Profit should reflect the future revenue generating ability and expense requirements of the operating business that comprise the ongoing operations of the company.
\(\text{NOPM} = \frac{\text{Revenue} - \text{Expenses}}{\text{Revenue}}\)
Tax payments are taken from the consolidated income statement, provision for income taxes. The effect of taxes on profits is accounted for.
\(\text{Tax rate} = \frac{\text{Income taxes}}{\text{Income before income taxes}}\)
To get future EPS
\(\text{future EPS} = \frac {\text{future revenue} \times \text{NOPM} \times \text{(1-tax rate)}}{\text{number of shares}}\)
Use polyfit to get revenue least square fit
= df_dcf_data['revenue']/1e6
y = np.arange(len(y))
x = np.polyfit(x, y, 1)
m, c print('revenue slope: {:.2f}'.format(m))
print('revenue intercept: {:.2f}'.format(c))
= m*x + c # data points for each year lstsq_fit
revenue slope: 41.61
revenue intercept: 530.32
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'dollars, $M')
ax1.set_ylabel(
'FY'],df_dcf_data['revenue']/1e6, 'o',label='revenue')
ax1.plot(df_metrics_data['FY'],lstsq_fit, '-',label='least squares fit')
ax1.plot(df_metrics_data[
='y')
ax1.tick_params(axis#ax1.set_ylim((0,4))
ax1.legend()
plt.grid()
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Revenue and least squares fit')
plt.title( plt.show()
Using the equation for the best fit line, find the y value for the EPS point at five years in the future.
# estimated revenue in 5 years
= m*(x[-1]+5) + c
rev_5yr_est print('estimated rev in 5 years: ${:,.1f}M'.format(rev_5yr_est))
estimated rev in 5 years: $1,237.6M
Note: might need to include estimate of number of shares outstanding in 5 years.
print('starting revenues: ${:,.2f}'.format(rev_start/1e9))
starting revenues: $1.07
Using the adjusted NOPM and tax rate from scenario 1.
adjusted DCF input values and rates
= isv_s1_nopm # use nopm from scenario 1
pm_nopm = isv_s1_tr # use tr from scenario 1
pm_tax_rate = rev_5yr_est*pm_nopm*(1-pm_tax_rate)*1e6/df_dcf_data['shares_outstanding'].iloc[-1]
pm_eps_5yr_est #pm_eps_5yr_est = rev_5yr_est*nopm_avg*1e6/df_dcf_data['shares_outstanding'].iloc[-1]
print('using preferred method: estimated eps in 5 years: ${:.2f}'.format(pm_eps_5yr_est))
using preferred method: estimated eps in 5 years: $9.13
Using the high and low price to earning ratio from above and the projected EPS, calculate the range of stock price in five years.
= pm_eps_5yr_est*pe_avg_low
naic_price_pm_low = pm_eps_5yr_est*pe_avg_high
naic_price_pm_high print('estimated price range in 5 years from preferred method: {:.2f} to {:.2f}'.format(
naic_price_pm_low,naic_price_pm_high))
estimated price range in 5 years from preferred method: 306.95 to 417.19
Observation:
Based on revenue growth, the projected stock price is higher than the current price. However, based on price history, the stock is not expected to appreciate.
6) Future stock price
The projected future stock price is estimated from the results shown in this notebook based on DCF intrinsic stock value, the NAIC method or a combination of both. The DCF method does not consider market sentiment or popularity of the stock, whereas the NAIC method looks at the PE and EPS to develop the historical consensus that the market has put on the price of the stock. Both the NAIC and the DCF valuation should be considered. The DCF valuation is of the current ISV which is used as an indication of the future value, since it is assumed that the market price will converge eventually to the intrinsic value.
The estimated future stock price considers the following:
- base case ISV - Senario ISV - NAIC EPS growth - NAIC preferred method
Using 5 year NAIC as a conservative estimate for the 10 year value and the analysis results, a judgment call is made concerning the price to put on the future value of the stock.
print('estimated price range in 5 years from EPS: ${:.2f} to ${:.2f}'.format(naic_price_eps_low,naic_price_eps_high))
print('estimated price range in 5 years from preferred method: ${:.2f} to ${:.2f}'.format(
naic_price_pm_low,naic_price_pm_high))
print('intrinsic stock value, baseline case: ${:,.2f}'.format(isv_baseline))
print('intrinsic stock value, scenario 1 case: ${:,.2f}'.format(isv_S1))
print('current stock price: ${:,.2f}'.format(csp))
estimated price range in 5 years from EPS: $157.04 to $213.44
estimated price range in 5 years from preferred method: $306.95 to $417.19
intrinsic stock value, baseline case: $66.99
intrinsic stock value, scenario 1 case: $46.32
current stock price: $97.74
The estimated price range in 5 years from the preferred method is \$86.14 to \$116.85. Taking the average and using that value on the IRR calculations.
Using the average of:
- low estimated price from EPS and the low - estimated price from the preferred method - intrinsic stock value, scenario 1 case
use average of NAIC low price
#fsp = (naic_price_eps_low + naic_price_pm_low + csp)/3 # estimated future stock price
= (naic_price_eps_low) # estimated future stock price
fsp print('estimated future stock price: ${:,.2f}'.format(fsp))
estimated future stock price: $157.04
7) Dividend payout
The dividend payout examines the amount shareholders are getting from the company relative to earnings or revenue. It is an important metric to determine how the business is operating and whether it has enough growth potential.
Dividend history
# calculate the percent change in dividends
= np.zeros(len(df_metrics_data['dps'])) # percent change in dividend
pcd for i in range(len(df_metrics_data['dps'][0:-1])):
+1] = ((df_metrics_data['dps'][i+1] - df_metrics_data['dps'][i])/
pcd[i'dps'][i+1])*100
df_metrics_data[
= 100
width
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'Dividend per share, $')
ax1.set_ylabel(
# plot revenue as single bar
'FY'],df_metrics_data['dps'], width,color='k')
plt.bar(df_metrics_data[
='y')
ax1.tick_params(axis
plt.grid()
# instantiate a second y-axes that shares the same x-axis
= ax1.twinx()
ax2 = 'tab:green'
color
'+-g')
ax2.plot(year_ended_list,pcd,
'% Change in dividend',color=color)
ax2.set_ylabel(='y', labelcolor=color)
ax2.tick_params(axis0,20))
ax2.set_ylim((
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Dividend history per share')
plt.title( plt.show()
= pcd[-6:].mean() #last 6 years
adgr print('average dividend growth rate: {:.2f}%'.format(adgr))
average dividend growth rate: 2.00%
Dividend yield
Dividend yield equals the annual dividend per share divided by the stock’s price per share. The plot below shows the history of dividend yield over the evaluation period.
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= 50 # the width of the bars
width 'FY'],(df_metrics_data['dps']/fy_high-df_metrics_data['dps']/fy_low)*100,
plt.bar(df_metrics_data[=df_metrics_data['dps']/fy_low*100,label='yield')
width,bottom= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
1,10))
plt.ylim(('Range of dividend yield each year')
plt.title('dividend yield, %')
plt.ylabel(#plt.legend()
plt.grid()
# show plot
plt.show()
The dividend yield for the past five years has been in the 2.5 to 3.5 percent range.
Dividend payout ratio
The dividend payout ratio is a relative measure of how much the company is paying to shareholders in dividends compared to other metrics such as revenue, earnings or cash flow. The dividend payout ratio is plotted as a ratio of dividends to net income, free cash flow (Net cash provided by operating activities) and NOP. The payout ratio is useful for assessing a dividend’s sustainability. Companies are extremely reluctant to cut dividends since it can drive the stock price down and reflect poorly on management’s abilities.
Payout ratio using net income
Payout ratio using net income plots the ratio of dividend payout divided by net income:
\(\frac {\text{Dividends}}{\text{Net income}}\)
Depending on how net income is listed in the financial statements, it may include large other charges.
Payout ratio using cash flow
Payout ratio using net cash flow plots the ratio of dividend payout divided by cash flow:
\(\frac {\text{Dividends}}{\text{cash flow}}\)
Cash flow from operating activities usually includes a long list of items. Some insight might be obtained from this ratio. The trend should be consistent.
Payout ratio using NOP
Payout ratio using NOP plots the ratio of dividend payout divided by NOP:
\(\frac {\text{Dividends}}{\text{NOP}}\)
NOP is calculated above and might be different from net income listed in the financial statements. This ratio should be the lowest numerically of the three plots.
Adjustments for a REIT
Dividend payout ratio does not apply to a REIT because of the 90% payout requirement.
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'Payout ratio')
ax1.set_ylabel(
'FY'],df_metrics_data['dividends']/df_metrics_data['net_income'], '-+',
ax1.plot(df_metrics_data[='Payout ratio using net income')
label'FY'],df_metrics_data['dividends']/df_metrics_data['free_cash_flow'], '-*',
ax1.plot(df_metrics_data[='Payout ratio using cash flow')
label'FY'],df_metrics_data['dividends']/nop, '-+',
ax1.plot(df_metrics_data[='dividends/NOP')
label
='y')
ax1.tick_params(axis#ax1.set_ylim((0,5))
ax1.legend()
plt.grid()
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Payout ratio')
plt.title( plt.show()
# average the last three years
print('Dividends are paid at {:.1f}% of net income'.format(
'dividends']/df_metrics_data['net_income'])[-3:].mean()*100))
(df_metrics_data[print('Dividends are paid at {:.1f}% of cash flow'.format(
'dividends']/df_metrics_data['free_cash_flow'])[-3:].mean()*100))
(df_metrics_data[print('Dividends are paid at {:.1f}% of NOP'.format((df_metrics_data['dividends']/nop)[-3:].mean()*100))
Dividends are paid at 150.5% of net income
Dividends are paid at 75.4% of cash flow
Dividends are paid at 58.0% of NOP
Payout ratio using net income: Payout ratio using cash flow:
Payout ratio using NOP:
Internal Rate of Return (IRR) calculations
The internal rate of return (IRR) is the discount rate that makes the net present value (NPV) of all cash flows equal to zero in a discounted cash flow analysis. Generally speaking, the higher an internal rate of return, the more desirable an investment is to undertake.
As explained above, the stock price has not changed by much over the years, even though the revenue and dividends have been increasing. The final stock price is set equal to the current price.
Using the average dividend growth rate calculated above, a series of estimated future dividend payments are generated.
= np.zeros(len(df_metrics_data['dps'])) # future dividend payments
fdp 0] = df_metrics_data['dps'].iat[-1]
fdp[for i in range(len(df_metrics_data['dps'][0:-1])):
+1] = fdp[i]+fdp[i]*adgr/100 fdp[i
print('current stock price: ${:,.2f}'.format(csp))
#fsp = 100 #csp #500 #(csp + 102.05 + 138.82)/3 # final stock price, $
print('final stock price: ${:,.2f}'.format(fsp))
current stock price: $97.74
final stock price: $157.04
= np.copy(fdp) # make a copy of the estimated cash flow
est_cf
# cash flows, initial purchase, dividend payments and final sale
0] = est_cf[0] - csp # subtract purchase price from the first dividend payment
est_cf[-1] = est_cf[-1] + fsp # include the sale price with the final dividend payment est_cf[
= np.irr(est_cf)
dividend_irr print('Dividend IRR: {:.2f}%'.format(dividend_irr*100))
Dividend IRR: 8.57%
According to global investment bank Goldman Sachs, 10-year stock market returns have averaged 9.2% over the past 140 years. and according to 10-Year Annualized Rolling Returns, the long term average is about 10%. However there are many years where the rolling 10 year average return is below 4%.
The calculated IRR is 8%, which is a decent return and significantly higher than current interest rates.
8) Management performance
The following analysis somewhat follows the Warren Buffett strategy as outlined in [3]. This strategy is essentially value investing where companies are chosen that meet a set of criteria and who’s stock price is below the intrinsic value plus a margin of safety. These investments are usually held for the long term.
- Financial metrics
The following analysis looks at financial ratios over the evaluation period. Financial ratios can be used to judge management performance. Consistent favorable trends are an indication that management is taking care of the company. - Total liabilities to total assets ratio
- Debt to equity and debt to NOP ratios
- Financial ratios: RoE, RoA and PM
- NAIC section 2: Evaluating management
- Normalized data from consolidated statements
- Market metrics
- One dollar premise
- Share price vs EPS
- Market capitalization
- Qualitative metrics
- Simple and understandable business model
- Favorable long term prospects
- Commodity reliance
- Consistent operating history
- rationality:
- focus on core aspects
- only invest in high ROE businesses
- focus on shareholder equity
Financial metrics
The following financial metrics are examined over the evaluation period. We are looking for favorable trends and evidence of consistent operations. Some red flags will also be evident in the plots.
Red flags:
- Shrinking gross profit margin - Receivables growing faster than sales - Rising debt-to-equity ratio - Several years of revenue trending down - Unsteady cash flow - Rising accounts receivable or inventory in relation to sales - Rising outstanding share count - Consistently higher liabilities than assets - Decreasing gross profit margin - Increasing revenue while cash flow remains the same - Unusual changes in key financial ratios
Total liabilities to total assets ratio
The ratio of liabilities to assets is plotted over the evaluation period. For most companies examined the liabilities are the total liabilities and the ratio is calculated using total assets and total tangible assets. Total tangible assets have goodwill and intangibles removed from the total. The ratio gives an indication of how much the company is worth versus how much the company owes. Ideally the ratio of liabilities to assets should be less than one.
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'ratio')
ax1.set_ylabel(
# plot liabilities
'FY'],df_metrics_data['total_liabilities']/df_metrics_data['total_assets'], '-+',
ax1.plot(df_metrics_data[='total liabilities to total assets')
label'FY'],df_metrics_data['total_liabilities']/df_metrics_data['total_tangible_assets'], '-*',
ax1.plot(df_metrics_data[='total liabilities to total tangible assets')
label
='y')
ax1.tick_params(axis0,1))
ax1.set_ylim((=(1.8, 1))
ax1.legend(bbox_to_anchor
plt.grid()
# instantiate a second y-axes that shares the same x-axis
= ax1.twinx()
ax2 = 'tab:green'
color
#ax2.plot(year_ended_list,pcd,'+-g')
'FY'],
ax2.plot(df_metrics_data['total_assets']-df_metrics_data['total_tangible_assets'])/df_metrics_data['total_assets']*100,
(df_metrics_data[':',color=color,label='intangible assets to total assets')
'% intangible assets',color=color)
ax2.set_ylabel(='y', labelcolor=color)
ax2.tick_params(axis0,100))
ax2.set_ylim((=(1.7, 0))
ax2.legend(bbox_to_anchor
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Total liabilities to total assets ratio')
plt.title( plt.show()
FRT has no intangible assets, total assets are the same as total tangible assets. Total liabilities to total assets ratio is about 60%.
Debt to equity and debt to NOP ratios
The debt-to-equity ratio (D/E) is another key characteristic Buffett considers carefully. Buffett prefers to see a small amount of debt so that earnings growth is being generated from shareholders’ equity as opposed to borrowed money. The D/E ratio is calculated as follows:
\(\text{Debt-to-Equity Ratio} = \frac {\text{Total Liabilities}} {\text{Shareholders' Equity}} \text{ OR } \frac {\text{Long term debt}} {\text{Shareholders' Equity}}\)
This ratio shows the proportion of equity and debt the company uses to finance its assets, and the higher the ratio, the more debt—rather than equity—is financing the company. A high debt level compared to equity can result in volatile earnings and large interest expenses. For a more stringent test, investors sometimes use only long-term debt instead of total liabilities in the calculation above.
D/E is the traditional way to look at a company’s debt. Some rules of thumb say that the D/E should not be above 2 or 3. However the D/E company’s typically vary by industry. The ratio of LT debt to NOP gives the number of years it would take the company to pay back debt from NOP, the lower the number the shorter amount of time.
\(\text{Debt-to-NOP Ratio} = \frac {\text{Total Liabilities}} {\text{NOP}}\)
= df_metrics_data['total_tangible_assets'] - df_metrics_data['total_liabilities']
tangible_equity
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'ratio')
ax1.set_ylabel(
'FY'],df_dcf_data['long_term_debt']/df_metrics_data['shareholder_equity'],
ax1.plot(df_metrics_data['-^',label='(LT debt)/Equity')
#ax1.plot(year_ended_list,df_dcf_data['long_term_debt']/tangible_equity, '-',label='(LT debt)/(Tangible Equity)')
'FY'],df_metrics_data['total_liabilities']/df_metrics_data['shareholder_equity'],
ax1.plot(df_metrics_data['-*',label='(total liabilities)/Equity')
#ax1.plot(year_ended_list,total_liabilities/BV, '-^',label='(total liabilities)/BV')
'FY'],df_metrics_data['total_liabilities']/nop, '-+',label='(total liabilities)/NOP')
ax1.plot(df_metrics_data[#ax1.plot(year_ended_list,total_liabilities/net_income, '-+',label='(total liabilities)/(net income)')
#ax1.plot(year_ended_list,df_dcf_data['current_liabilities']/nop, '-*',label='(current liabilities)/NOP')
#ax1.plot(year_ended_list,Liabilities_wo_deposits/nop, '-+',label='(Liabilities w/o deposits)/NOP')
='y')
ax1.tick_params(axis#ax1.set_ylim((0,10))
#ax1.legend()
=(1.6, 1))
ax1.legend(bbox_to_anchor
plt.grid()
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Various debt ratios')
plt.title( plt.show()
(LT debt)/Equity is plotted and is below 2 for each year in the evaluation period. A threshold of 2 is traditionally the upper limit for a reasonable amount of debt that a company should carry.
(total liabilities)/Equity is plotted and except for 2020 has been below the threshold of 2.
(total liabilities)/NOP to is plotted for each year in the evaluation period is below 10. A value of 10 has been chosen as the threshold for this ratio and indicates how many years it would take the company to pay off total liabilities from the NOP generated each year. A threshold of ten seems like a reasonable level of debt measured against NOP.
Financial ratios
Various ratios can be used to judge management performance. Consistent favorable trends are an indication that management is taking care of the company.
Return on equity
Sometimes return on equity (RoE) is referred to as stockholder’s return on investment. It reveals the rate at which shareholders earn income on their shares. Buffett always looks at RoE to see whether a company has consistently performed well compared to other companies in the same industry. RoE is calculated as follows:
\(\text{Return on Equity} = \frac {\text{Net Income}} {\text{Shareholder's Equity}}\)
Looking at the RoE in just the last year isn’t enough. The investor should view the RoE from the past five to 10 years to analyze historical performance.
\(\text{Shareholders’ Equity} = \text{Total Assets} − \text{Total Liabilities}\)
For this company, this method of getting Shareholders’ Equity gives negative values. On the Consolidated Balance Sheets, there is a line for Total stockholders’ equity, which is used.
Return on Assets
Return on assets is a profitability ratio that provides how much profit a company is able to generate from its assets. In other words, return on assets (RoA) measures how efficient a company’s management is in generating earnings from their economic resources or assets on their balance sheet.
\(\text{Return on assets} = \frac {\text{Net Income}} {\text{Total Assets}}\)
Calculating the RoA of a company can be helpful in comparing a company’s profitability over multiple quarters and years as well as comparing to similar companies. However, it’s important to compare companies of similar size and industry.
For example, banks tend to have a large number of total assets on their books in the form of loans, cash, and investments. A large bank could easily have over \$2 trillion in assets while putting up a net income that’s similar to companies in other industries. Although the bank’s net income or profit might be similar to an unrelated company and the bank might have high-quality assets, the bank’s RoA will be lower. The larger number of total assets must be divided into the net income, creating a lower RoA for the bank.
Similarly, auto manufacturing requires huge facilities and specialized equipment. A lucrative software company that sells downloadable programs online may generate the same net profits, but it could have a significantly higher RoA than its more asset-heavy counterparts. When utilizing this metric to compare productivity across businesses, it’s important to take into account what types of assets are required to function in a given industry, rather than simply comparing the figures.
Profit Margin
A company’s profitability depends not only on having a good profit margin, but also on consistently increasing it. This margin is calculated by dividing net income by net sales. For a good indication of historical profit margins, investors should look back at least five years. A high-profit margin indicates the company is executing its business well, but increasing margins mean management has been extremely efficient and successful at controlling expenses.
\(\text{Profit Margin} = \frac {\text{Net Income}} {\text{Revenue}}\)
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
= plt.subplots()
fig, ax1 'percent')
ax1.set_ylabel(
'FY'],df_metrics_data['net_income']/df_metrics_data['shareholder_equity']*100,
ax1.plot(df_metrics_data['-+',label='RoE')
'FY'],df_metrics_data['net_income']/df_metrics_data['total_assets']*100,
ax1.plot(df_metrics_data['-*',label='RoA')
#ax1.plot(df_metrics_data['FY'],total_liabilities/shareholder_equity, '-^',label='D/E')
'FY'],df_metrics_data['net_income']/df_dcf_data['revenue']*100,
ax1.plot(df_metrics_data['-^',label='Profit margin')
='y')
ax1.tick_params(axis#ax1.set_ylim((0,14))
#ax1.legend()
=(1.05, 1))
ax1.legend(bbox_to_anchor
plt.grid()
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
'Various ratios')
plt.title( plt.show()
Observation:
The trends for RoE, RoA and profit margin are shown above. The effect of the acquisition of Celgene has caused these ratios to go negative in 2020. From 2016 to 2019, these ratios had wide variation year to year, with 2017 showing a decline followed by a large increase the following year. Normally steady performance is better than the erratic variation shown here.
NAIC section 2: Evaluating management
See page 86, figure 9-1.
- % pretax profit on sales, (net before taxes)/rev - % earned on equity (another way of saying RoE, using calculated equity)
Percent earned on equity is a measure of financial performance calculated by dividing net income by equity. Because equity is equal to a company’s assets minus its debt, percent earned on equity is considered the return on net assets. Percent earned on equity is considered a gauge of a corporation’s profitability and how efficient it is in generating profits.
This section is not applicable to a REIT, since income before taxes and income tax are not considered since the values are low.
Set the locator
locator = mdates.YearLocator() # every year fmt = mdates.DateFormatter(‘%Y’)
fig, ax1 = plt.subplots() ax1.set_ylabel(‘percent’) #ax1.plot(year_ended_list,net_income, ‘-+’,label=‘net income’) ax1.plot(df_metrics_data[‘FY’],df_dcf_data[‘income_before_income_taxes’]/df_dcf_data[‘revenue’]100, ‘-+’, label=‘income before taxes/rev’) #ax1.plot(year_ended_list,df_dcf_data[‘revenue’], ‘-+’,label=‘revenue’) #ax1.plot(year_ended_list,free_cash_flow, ’-’,label=‘free cash flow’)
ax1.tick_params(axis=‘y’) #ax1.set_ylim((0,18)) #ax1.legend() plt.grid()
X = plt.gca().xaxis X.set_major_locator(locator) # Specify formatter X.set_major_formatter(fmt) plt.gcf().autofmt_xdate()
plt.title(‘% pretax profit on sales’) plt.show()
Over the years 2016 to 2020, pretax profit on sales has a downward trend. Ideally this trend should be increasing or at least flat.
Set the locator
locator = mdates.YearLocator() # every year fmt = mdates.DateFormatter(‘%Y’)
fig, ax1 = plt.subplots() ax1.set_ylabel(‘percent’)
#ax1.plot(year_ended_list,shareholder_equity/df_dcf_data[‘revenue’]100, ‘-+k’, # label=‘shareholder equity/rev’) #ax1.plot(year_ended_list,net_income/shareholder_equity100, ‘-+’,label=‘RoE’) ax1.plot(df_metrics_data[‘FY’], df_metrics_data[‘net_income’]/(df_metrics_data[‘total_assets’]-df_metrics_data[‘total_liabilities’])*100, ‘-+’,label=‘RoE’)
ax1.tick_params(axis=‘y’) #ax1.set_ylim((0,12)) #ax1.legend() plt.grid()
X = plt.gca().xaxis X.set_major_locator(locator) # Specify formatter X.set_major_formatter(fmt) plt.gcf().autofmt_xdate()
plt.title(‘% earned on equity’) plt.show()
Percent earned on equity (another way of saying RoE). Percent earned on equity trend has been flat up to 2015, then became erratic.
Percent earned on equity is a measure of financial performance calculated by dividing net income by equity. Because equity is equal to a company’s assets minus its debt, percent earned on equity is considered the return on net assets. Percent earned on equity is considered a gauge of a corporation’s profitability and how efficient it is in generating profits.
Plot normalized data from consolidated statements
The following charts examine data from the consolidated financial statements and compare normalized trends over the evaluation period. The first chart plots normalized revenue along with normalized EPS, NOP and free cash flow. All values are normalized to the starting value in the series. Change relative to the normalized starting value can be seen over the evaluation period. Ideally the normalized parameters plotted should track revenue. Any large departures indicate an area of concern.
Normalized consolidated statement of income
The following chart shows normalized revenue plotted with normalized parameters from the consolidated statement of income.
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
# set look back range, left_yr is the index into the date range
= -11
left_yr
'FY'][left_yr:],
plt.plot(df_metrics_data['revenue'][left_yr:]/df_dcf_data['revenue'].iloc[left_yr],'^-',label='Revenue')
df_dcf_data['FY'][left_yr:],
plt.plot(df_metrics_data['eps'][left_yr:]/df_metrics_data['eps'].iloc[left_yr],
df_metrics_data['-.',label='EPS')
'FY'][left_yr:],
plt.plot(df_metrics_data[/nop[left_yr],
nop[left_yr:]'-.',label='NOP')
'FY'][left_yr:],
plt.plot(df_metrics_data['free_cash_flow'][left_yr:]/df_metrics_data['free_cash_flow'].iloc[left_yr],
df_metrics_data['-.',label='Free cash flow')
# net income
'FY'][left_yr:],
plt.plot(df_metrics_data['net_income'][left_yr:]/df_metrics_data['net_income'].iloc[left_yr],
df_metrics_data['-.',label='Net income')
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()# Changes x-axis range
-1])
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[
#plt.ylim((0,4))
'Normalized income statement data')
plt.title('normalized')
plt.ylabel(#plt.legend()
=(1.6, 1))
plt.legend(bbox_to_anchor
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
The plot above shows a steady increase in NOP, free cash flow and EPS until 2018. Since then the EPS have been eratic and the there was a down turn in revenue in 2020.
Normalized income statement 5 year look back
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
# set look back range, left_yr is the index into the date range
= -6
left_yr
'FY'][left_yr:],
plt.plot(df_metrics_data['revenue'][left_yr:]/df_dcf_data['revenue'].iloc[left_yr],'^-',label='Revenue')
df_dcf_data['FY'][left_yr:],
plt.plot(df_metrics_data['eps'][left_yr:]/df_metrics_data['eps'].iloc[left_yr],
df_metrics_data['-.',label='EPS')
'FY'][left_yr:],
plt.plot(df_metrics_data[/nop[left_yr],
nop[left_yr:]'-.',label='NOP')
'FY'][left_yr:],
plt.plot(df_metrics_data['free_cash_flow'][left_yr:]/df_metrics_data['free_cash_flow'].iloc[left_yr],
df_metrics_data['-.',label='Free cash flow')
# net income
'FY'][left_yr:],
plt.plot(df_metrics_data['net_income'][left_yr:]/df_metrics_data['net_income'].iloc[left_yr],
df_metrics_data['-.',label='Net income')
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()# Changes x-axis range
-1])
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[
#plt.ylim((0,4))
'Normalized income statement data')
plt.title('normalized')
plt.ylabel(#plt.legend()
=(1.6, 1))
plt.legend(bbox_to_anchor
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
Normalized consolidated balance sheet
The following chart shows normalized revenue plotted with normalized parameters from the consolidated balance sheet.
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
# set look back range, left_yr is the index into the date range
= -11
left_yr
'FY'][left_yr:],
plt.plot(df_metrics_data['revenue'][left_yr:]/df_dcf_data['revenue'].iloc[left_yr],'^-',label='Revenue')
df_dcf_data['FY'][left_yr:],
plt.plot(df_metrics_data['total_liabilities'][left_yr:]/df_metrics_data['total_liabilities'].iloc[left_yr],
df_metrics_data['-.',label='Total liabilities')
'FY'][left_yr:],
plt.plot(df_metrics_data['total_assets'][left_yr:]/df_metrics_data['total_assets'].iloc[left_yr],
df_metrics_data['-.',label='Total assets')
'FY'][left_yr:],
plt.plot(df_metrics_data['total_tangible_assets'][left_yr:]/df_metrics_data['total_tangible_assets'].iloc[left_yr],
df_metrics_data['-.',label='Total tangible assets')
'FY'][left_yr:],
plt.plot(df_metrics_data['long_term_debt'][left_yr:]/df_dcf_data['long_term_debt'].iloc[left_yr],'-.',label='Long term debt')
df_dcf_data['FY'][left_yr:],
plt.plot(df_metrics_data['current_liabilities'][left_yr:]/df_dcf_data['current_liabilities'].iloc[left_yr],
df_dcf_data['-.',label='Current liabilities')
'''
plt.plot(df_metrics_data['FY'][left_yr:],
df_dcf_data['current_assets'][left_yr:]/df_dcf_data['current_assets'].iloc[left_yr],
'-.',label='Current assets')
'''
# Depreciation and amortization
'FY'][left_yr:],
plt.plot(df_metrics_data['depreciation'][left_yr:]/df_dcf_data['depreciation'].iloc[left_yr],'-.',label='Depreciation & amortization')
df_dcf_data[
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()# Changes x-axis range
-1])
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[
#plt.ylim((0,4))
'Normalized balance statement data')
plt.title('normalized')
plt.ylabel(#plt.legend()
=(1.6, 1))
plt.legend(bbox_to_anchor
plt.grid()
# show plot
plt.show()
Observation
The plot above shows an increase in current liabilites relative to revenue in 2011. Then in 2019 there was a departure of the balance sheet items from revenue.
removed current assets because of spike in 2020.
Normalized balance statement 5 year look back
# Set the locator
= mdates.YearLocator() # every year
locator = mdates.DateFormatter('%Y')
fmt
# set look back range, left_yr is the index into the date range
= -6
left_yr
'FY'][left_yr:],
plt.plot(df_metrics_data['revenue'][left_yr:]/df_dcf_data['revenue'].iloc[left_yr],'^-',label='Revenue')
df_dcf_data['FY'][left_yr:],
plt.plot(df_metrics_data['total_liabilities'][left_yr:]/df_metrics_data['total_liabilities'].iloc[left_yr],
df_metrics_data['-.',label='Total liabilities')
'FY'][left_yr:],
plt.plot(df_metrics_data['total_assets'][left_yr:]/df_metrics_data['total_assets'].iloc[left_yr],
df_metrics_data['-.',label='Total assets')
'FY'][left_yr:],
plt.plot(df_metrics_data['total_tangible_assets'][left_yr:]/df_metrics_data['total_tangible_assets'].iloc[left_yr],
df_metrics_data['-.',label='Total tangible assets')
'FY'][left_yr:],
plt.plot(df_metrics_data['long_term_debt'][left_yr:]/df_dcf_data['long_term_debt'].iloc[left_yr],'-.',label='Long term debt')
df_dcf_data['FY'][left_yr:],
plt.plot(df_metrics_data['current_liabilities'][left_yr:]/df_dcf_data['current_liabilities'].iloc[left_yr],
df_dcf_data['-.',label='Current liabilities')
'''
plt.plot(df_metrics_data['FY'][left_yr:],
df_dcf_data['current_assets'][left_yr:]/df_dcf_data['current_assets'].iloc[left_yr],
'-.',label='Current assets')
'''
# Depreciation and amortization
'FY'][left_yr:],
plt.plot(df_metrics_data['depreciation'][left_yr:]/df_dcf_data['depreciation'].iloc[left_yr],'-.',label='Depreciation & amortization')
df_dcf_data[
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()# Changes x-axis range
-1])
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[
#plt.ylim((0,4))
'Normalized balance statement data')
plt.title('normalized')
plt.ylabel(#plt.legend()
=(1.6, 1))
plt.legend(bbox_to_anchor
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
Looking back over a six year period, total liabilities has diverged from revenue.
Market metrics
The share price is determined by the market. The value is determined by the analyst.
One dollar premise
This is a financial test that shows the strength of the business and how well management has rationality allocated to the company’s business.
From a company’s income, subtract all dividends paid to shareholders. What is left over is the company’s retained earnings. Now add the company’s retained earnings over a 10 year period. Next determine the difference between the company’s current market value and its market value 10 years ago. If the business has employed retained earnings unproductively over this ten year period, the market eventually catches up and will set a lower price on the business.
= df_metrics_data['net_income'].sum() - df_metrics_data['dividends'].sum()
retained_earnings print('retained earnings: ${:,.2f}B'.format(retained_earnings/1e9))
retained earnings: $-0.38B
Retained earnings are negative. This indicates either some bad data or something fundamentally wrong.
Net income data is from the income statement under Net (Loss)/Earnings Attributable to BMY and Dividend data is from the cash flow sheet under financial activities. For this company,the net income for many years is less than the dividends paid, which results in negative retained earnings. I think this is an item of concern.
# Current market value, share price multiplied by number of shares
= df_dcf_data['shares_outstanding'].iloc[-1]*fy_high[-1]
cmv_high = df_dcf_data['shares_outstanding'].iloc[-1]*fy_low[-1]
cmv_low print('Current market value: ${:,.2f}B to ${:,.2f}B'.format(cmv_low/1e9,cmv_high/1e9))
Current market value: $7.15B to $11.34B
# Past market value, share price multiplied by number of shares
= df_dcf_data['shares_outstanding'].iloc[0]*fy_high[0]
pmv_high = df_dcf_data['shares_outstanding'].iloc[0]*fy_low[0]
pmv_low print('Past market value: ${:,.0f}B to ${:,.0f}B'.format(pmv_low/1e9,pmv_high/1e9))
Past market value: $4B to $5B
print('Difference in market value: ${:,.0f}B to ${:,.0f}B'.format((cmv_low-pmv_low)/1e9,(cmv_high-pmv_high)/1e9))
Difference in market value: $3B to $6B
This difference in market value is greater than the retained earnings.
Market capitalization
Total value of common equity is calculated using the DCF model from scenario 1 inputs and it is a constant value for the year. The daily market capitalization is calculated from the formula:
\(\text{Market capitalization} = \text{(daily closing share price)} \times \text{(number of shares outstanding)}\)
Market capitalization refers to the total dollar market value of a company’s outstanding shares of stock. It measures the cost of buying all of a company’s shares. Comparing this value to the intrinsic value calculated from the DCF model shows whether the company can be purchased at a discount to its value.
#so = df_dcf_data['shares_outstanding'].iloc[-1] # shares outstanding
print('shares outstanding, basic: {:,.0f}'.format(so))
# get starting and ending dates for last calendar year in datetime format
= year_ended_list[-1]
start = start + relativedelta(years=1)
end = df_price_history.truncate(before=start, after=end)
p1
# print total value of common equity for base case and scenario
print('total value of common equity, baseline case: ${:,.2f}B'.format(tvce_baseline/1e9))
print('total value of common equity, scenario 1: ${:,.2f}B'.format(tvce_S1/1e9))
#print('average value of common equity (base & scenario): ${:,.2f}B'.format((tvce_S1+tvce_base)/2/1e9))
# make an array
= np.ones(len(p1))*tvce_S1 # the value is constant across all dates
total_value_S1
# add scenario 1 total value of common equity data to dataframe
#p1['total value avg'] = total_value_avg
'total value S1'] = total_value_S1 p1[
shares outstanding, basic: 81,353,180
total value of common equity, baseline case: $5.45B
total value of common equity, scenario 1: $3.77B
# Set the locator
= mdates.MonthLocator() # every month
locator = mdates.DateFormatter('%b-%Y')
fmt
'Close']*so/1e9,label='Market capitalization')
plt.plot(p1[#plt.plot(p1['total value base']/1e9,label='total value of common equity, base')
'total value S1']/1e9,label='Total value of common equity, scenario 1')
plt.plot(p1['total value S1']/1e9/0.7,'-.',label='70% threshold decision model')
plt.plot(p1[
= plt.gca().xaxis
X
X.set_major_locator(locator)# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
#plt.ylim((2,8))
'Market Cap and total value of common equity')
plt.title('dollars, $B')
plt.ylabel(=(1.1, 1))
plt.legend(bbox_to_anchor#plt.legend()
plt.grid()
# show plot
plt.show()
As shown in the plot, the company’s market capitalization is higher than the total value of common equity as calculated using scenario 1 DCF data. This implies that the company is overvalued.
Qualitative metrics
Beyond the numbers in the financial statements, there are metrics that are qualitative in nature that are important to the investor. These are subjective measures of business and management operations that influence value. In this section a few qualitative metrics are discussed below.
Simple and understandable business model
Favorable long term prospects
Commodity reliance
By commodity is meant a product or service that is easily reproducible by a competitor.Consistent operating history
The trends for RoE, RoA and profit margin have been plotted above.Rationality
Do comments and discussion made by the management in the annual reports reflect an optimal level of benefit or utility to the company over the long term?
Focus on core aspects
Only invest in high ROE businesses
Focus on shareholder equity
Back to Contents
9) Decision model
The decision model establishes thresholds that are to used in the purchase decision. There are three hard decision thresholds in this model which are:
1. Intrinic value 2. Debt 3. Dividend payout ratio 4. Dividend IIR
The first threshold is based on the intrinisic value of the company as calculated by the DCF model semario 1. Reconizing that absolute intrinsic value is an elusive concept, judgement, justified by facts (assets, earnings, dividends, debt and cash flow), establishes the value by adjusting various rates, based on judgement and using a five year forward projection period. This should give a intrinsic value that is based on the historical data, modified by judgement.
I’m using a threshold of the intrinsic value calculated in senario 1 (isv_S1) that is greater than 70% of the current stock price, provided that the NAIC valuation is above the current stock price. This accounts for the inadequacy or incorrectness of the data, the uncertainties of the future, and considers the behavior of the market.
The second threshold is the level of debt. The ratios of (LT debt)/Equity, (total liabilities)/Equity and (total liabilities)/NOP are ploted for the evaluation period. Over the evaluation period the (LT debt)/Equity and (total liabilities)/Equity should be less than 2 and stable. A threshold of 2 has been discussed in the litureture as a level of debt that a company can reasonably take on.
The thereshold for (total liabilities)/NOP is set at 10. This means that the company can pay off all the liabilities with tens years worth of NOP, which seems like a reasonable timeframe for an established and stable company.
The third threshold is the dividend payout ratio and is a relative measure of how much the company is paying to shareholders in dividends compared to the metrics of NOP and free cash flow (Net cash provided by operating activities). The payout ratio is useful for assessing a dividend’s sustainability. Payout ratio for a REIT is established by tax law and not used as an evaluation criteria. For other industries a threshold of 50% has been set as the limit.
The dividend IRR threshold is the internal rate of return for investor dividend cash flow (divident_irr) should be greater than 10 year treasury bond yield (tby) plus the equity risk premium (eq_rp). Otherwise, other investment operatunities should be looked at.
In the decision model there are soft thresholds based on judgement. Soft thresholds are a collection of ratios and analysis that taken together tell a story of the performance of the conmpany and manatgments ability to run the company and support dividends over the long term. Use judgement and make an evalaution.
The third critiera is a collection of ratios and analysis that taken together tell a story of the performance of the conmpany and manatgments ability to run the company and support dividends over the long term. Use judgement and make an evalaution. These are the following:
1. Financial metrics 2. Market metrics 3. Qualitative metrics
The soft thresholds are discused in section 10.
Check DCF and NAIC value thresholds
# check DCF senario 1
= isv_S1/csp #ratio of isv to csp
dcf_score = 0.7
dcf_threshold if dcf_score < 0.7:
print('FAIL, DCF score is less than {:.1f} at {:.1f}'.format(dcf_threshold,dcf_score))
else:
print('PASS, DCF score is above {:.1f} at {:.1f}'.format(dcf_threshold,dcf_score))
# check NAIC
= np.array([naic_price_eps_low,naic_price_pm_low]).min()/csp
naic_score = 1
naic_threshold if naic_score < 1:
print('FAIL, NAIC score is less than {:.1f} at {:.1f}'.format(naic_threshold,naic_score))
else:
print('PASS, NAIC score is above {:.1f} at {:.1f}'.format(naic_threshold,naic_score))
# check both scores
if naic_score < 1 or dcf_score < 0.7:
print('One or both DCF and NAIC scores failed')
else:
print('Both DCF and NAIC scores pass')
FAIL, DCF score is less than 0.7 at 0.5
PASS, NAIC score is above 1.0 at 1.6
One or both DCF and NAIC scores failed
Check debt thresholds
= 4
debt_lookback = df_dcf_data['long_term_debt'][-debt_lookback:].mean()/df_metrics_data['shareholder_equity'][-debt_lookback:].mean()
avg_LT_debt2EQ = df_metrics_data['total_liabilities'][-debt_lookback:].mean()/df_metrics_data['shareholder_equity'][-debt_lookback:].mean()
avg_TLiability2EQ = df_metrics_data['total_liabilities'][-debt_lookback:].mean()/nop[-debt_lookback:].mean()
avg_TLiability2NOP
print('long term debt to shareholder equity ratio = {:.2f}'.format(avg_LT_debt2EQ))
print('total liabilities to shareholder equity ratio = {:.2f}'.format(avg_TLiability2EQ))
print('total liabilities to NOP ratio = {:.2f}'.format(avg_TLiability2NOP))
if (avg_LT_debt2EQ > 2) or (avg_TLiability2EQ > 2) or (avg_TLiability2NOP > 10):
print('FAILED one of the debt threshold limits')
long term debt to shareholder equity ratio = 1.56
total liabilities to shareholder equity ratio = 1.78
total liabilities to NOP ratio = 7.97
Check dividend payout and IIR thresholds
# check dividend payout ratio average the last three years
print('Dividends are paid at {:.1f}% of cash flow'.format(
'dividends']/df_metrics_data['free_cash_flow'])[-3:].mean()*100))
(df_metrics_data[print('Dividends are paid at {:.1f}% of NOP'.format((df_metrics_data['dividends']/nop)[-3:].mean()*100))
if ((df_metrics_data['dividends']/nop)[-3:].mean() or (df_metrics_data['dividends']/df_metrics_data['free_cash_flow'])[-3:].mean()) > 0.5:
print('FAIL, dividend payout ration too high')
Dividends are paid at 75.4% of cash flow
Dividends are paid at 58.0% of NOP
FAIL, dividend payout ration too high
# Check dividend IRR limit
if dividend_irr < (tby+eq_rp):
print('FAIL, dividend IRR is less than {:.2f} at {:.2f}'.format((tby+eq_rp)*100,dividend_irr*100))
else:
print('PASS, dividend IRR is above {:.2f} at {:.2f}'.format((tby+eq_rp)*100,dividend_irr*100))
PASS, dividend IRR is above 6.48 at 8.57
# check DCF senario 1
= isv_S1/csp #ratio of isv to csp
dcf_score = 0.7
dcf_threshold if dcf_score < 0.7:
print('FAIL, DCF score is less than {:.1f} at {:.1f}'.format(dcf_threshold,dcf_score))
else:
print('PASS, DCF score is above {:.1f} at {:.1f}'.format(dcf_threshold,dcf_score))
# check NAIC
= np.array([naic_price_eps_low,naic_price_pm_low]).min()/csp
naic_score = 1
naic_threshold if naic_score < 1:
print('FAIL, NAIC score is less than {:.1f} at {:.1f}'.format(naic_threshold,naic_score))
else:
print('PASS, NAIC score is above {:.1f} at {:.1f}'.format(naic_threshold,naic_score))
# check both scores
if naic_score < 1 and dcf_score < 0.7:
print('One or both DCF and NAIC scores failed')
else:
print('Both DCF and NAIC scores pass')
FAIL, DCF score is less than 0.7 at 0.5
PASS, NAIC score is above 1.0 at 1.6
Both DCF and NAIC scores pass
10) Conclusion
The following is a summary of the results described above:
- Stock screener results:. - Current news: - Review quarterly results: - Average daily volume: - Dividend yield: - Discounted cash flow analysis, baseline: - DCF Scenarios: Adjustments were made and the ISV
- NACI stock selection guide analysis: - Dividend payout: - Management performance:
- Financial metrics: - Market metrics: - Qualitative metrics: see above
Concerns:
Summary:
Recommendation: Don’t buy above $50 per share.
10) Notes
The following notes outline the changes to the DCF model for financial and REIT companies.
Valuing a REIT
Notes from Valuepro Book, page 237
- NOPM: To calculate operating income take rental revenue and subtracted total real estate expenses and G&A expenses. To arrive at the NOPM divide the adjusted income from real estate by real estate rental revenue. For the REIT, take income from real estate, which includes depreciation and amortization, and subtract GSA. Exclude other income, gains on sale of real estate and interest expenses.
- REIT has no traditional R&D costs
REIT is not taxed at the corporate level, tax rate: should be near zero.
Depreciation and capital expenditures are significantly higher for REITs than in other companies.
New property acquisitions are not directly accounted for in the DCF model for a REIT.
- Working capitol: accounts payable, rents and security deposits
- Short term assets: cash, rents and other receivables and prepaid expenses
- Short term liabilities: accounts payable, advance rents security deposits
Working capital is almost zero, which is similar to other financial companies.
The consolidated balance sheet lists the assets as: - Real estate held for investment, at cost: - Land - Buildings and improvements - Total real estate held for investment, at cost - Less accumulated depreciation and amortization - Real estate held for investment, net - Real estate and lease intangibles held for sale, net - Cash and cash equivalents <- current asset - Accounts receivable, net <- current asset - Lease intangible assets, net - Other assets, net
The line items indicated above have been taken to be the current assets. Intangibles and long term items have been excluded.
The consolidated balance sheet lists the liabilities as: - Distributions payable <- current liabilities - Accounts payable and accrued expenses <- current liabilities - Lease intangible liabilities, net - Other liabilities - Line of credit payable and commercial paper <- current liabilities - Term loans, net - Mortgages payable, net <- current liabilities - Notes payable, net
The line items indicated above have been taken to be the current liabilities.
Valuing a financial company
Notes from Valuepro Book, page 206
Total revenue comes from the total interest and dividend income line on the income statement. The calculation of operating income is more inclusive for a financial company than for an industrial or high tech company. For financial companies, operating revenue includes all normal revenue items plus interest income, dividends received and other investment income.
Cost of Goods Sold (CGS) comes from the Total interest expense line on the statement of income.
General and Administrative (G&A) are set to zero since they are included in the interest expense line
A financial company has no traditional R&D costs
\(\text{Cost of Goods Sold (CGS)} = \text{Total interest expense} + \text{Total non-interest expense}\)
General and Administrative (G&A) are set to zero since they are included in the interest expense line
A financial company has no traditional R&D costs
Depreciation and amortization of premises and equipment from Consolidated Statements of Cash Flows.
Amortization of other acquisition-related intangible assets is not included.
New investment and Depreciation: Property, plant and equipment expenditures and depreciation charges are significantly lower for a financial company. A typical manufacturing company, in order to grow its business, invests a significant portion of its revenues in plant, property and equipment (PPE). Financial companies invest very little in the way of PPE. However, software, risk management systems and acquisitions of other businesses, need to be included.
From the Consolidated Statements of Cash Flows, under Cash Flows from Investing Activities - Purchases of premises and equipment - Purchases of leased equipment, net
Working capital supports manufacturing and service activities of nonfinancial companies. For financial companies, their principal liabilities and assets are financial claims that take the place of working capital. Because there is no differentiation between current and long term assets and liabilities for a financial company, we adjust working capital charges to zero. A financial company generally invests all of its funds in other financial assets, which have characteristics of current assets rather than PP&E.
\(\text{Accounts Receivable} = 0\)
\(\text{Inventories} = 0\)
\(\text{Accounts Payable} = 0\)
\(\text{working capital} = 0\)Short term assets: The balance sheets of most financial companies do not separate assets and liabilities into current and long term categories. When calculating the short term assets take the total assets and subtract goodwill and intangible assets also subtract other assets of questionable value. Subtract long term assets such as PP&E from total assets.
\(\text{Short term assets} = \text{Total assets} - \text{good will and others of questionable value} - \text{Premises and equipment}\)
- A financial company’s principal liabilities are deposits, Federal funds purchased, trading account liabilities, insurance policy and claims reserves, contract holder funds and short term borrowing. To be consistent with the treatment of interest and an operating expense for financial companies, include long term debt in the short term liability category.
- Short term liabilities: Include long term debt.
\(\text{Long term debt} = 0\)
Excess return period
The excess return period is based on a judgment call. The authors of [2] use the 1-5-7-10 rule. They group companies into one of four general categories and excess return periods. They use a 10 year excess return period to calculate what they would consider the maximum value. They use a more conservative 1 year, 5 year or 7 year return period to calculate a more reasonable or minimum value.
- 1 year: Boring companies that operate in a highly competitive, low margin industry in which they have nothing particular going for them.
- 5 year: Decent companies that have a recognizable name and decent reputation and perhaps a regulatory benefit (utility company), but can’t control pricing or growth.
- 7 year: Good companies with good brand names, large companies of scale, good marketing channels and consumer identification (e.g. McDonald’s)
- 10 year: Great companies with great growth potential, tremendous marketing power, band names and in-place benefits (e.g. Intel, Microsoft, Coca Cola, Disney)
Notes about negative working capital
The company has a negative working capital rate. Negative working capital describes a situation where a company’s current liabilities exceed its current assets as stated on the firm’s balance sheet. In other words, there is more short-term debt than there are short-term assets.
Negative working capital most often arises when a business generates cash very quickly because it can sell products to its customers before it has to pay the bills to its vendors for the original goods or raw materials. In this way, the company is effectively using the vendor’s money to grow.
Dividend Aristocrat, Achiever & Champion
This company was selected for analysis because it is on the Dividend Aristocrat list and passes the quick look tests. This notebook will be used as a template when analyzing other companies.
- Aristocrat: S&P 500 Dividend Aristocrats is designed to measure the performance of S&P 500 index constituents that have followed a policy of consistently increasing dividends every year for at least 25 consecutive years.
- Achiever: The Broad Dividend Achievers Index. Eligible companies must be incorporated in the U.S. or its territories, trade on the NYSE, NASDAQ or AMEX, and have increased its annual regular dividend payments for the last 10 or more consecutive years.
- https://dividendvaluebuilder.com/dividend-achievers-list/
- https://www.marketbeat.com/dividends/achievers/
- Champion: This list includes companies that had increased their dividend for at least 25 consecutive years, and includes additional companies that had paid higher dividends without having increased the payout in every calendar year.
- https://dividendvaluebuilder.com/dividend-champions-list/
- https://www.dividendgrowthinvestor.com/p/dividend-champions-list.html
12) References
- Gray, Gary, et al. Streetsmart Guide to Valuing a Stock: the Savvy Investors Key to Beating the Market. McGraw-Hill, 2004.
- O’Hara, Thomas E., and Ken Janke. Starting and Running a Profitable Investment Club: the Official Guide from the National Association of Investors Corporation. Times Business, 1998.
- Robert G. Hagstrom, The Warren Buffett Way, Wiley, 2013